excel - how to have unlocked cells on a protected VBA created worksheet

Question

An input file of data is processed using VBA to create an Excel(2003) protected spreadsheet(Invoice). The spreadsheet is then to be distributed to other offices where some designated cells are to be amended. How can I create the worksheet to allow these cells to be amended when the whole sheet is protected? I have tried using the code below, and other similar variations, but it does not seem to work. Can you help?

Private Sub CellLock1()

  Cells.Select
  ' unlock all the cells
  Selection.Locked = False

  ' lock only these cells
  Range("J49:K49").Select
  Selection.Locked = True

 ActiveSheet.Protect DrawingObjects:=True, _
                     Contents:=True, _
                     Scenarios:=True, _
                     UserInterfaceOnly:=True, _
                     AllowFormattingCells:=True, _
                     AllowFormattingColumns:=True, _
                     AllowFormattingRows:=True, _
                     AllowInsertingColumns:=True, _
                     AllowInsertingRows:=True, _
                     AllowInsertingHyperlinks:=True, _
                     AllowDeletingColumns:=True, _
                     AllowDeletingRows:=True, _
                     AllowSorting:=True, _
                     AllowFiltering:=True, _
                     AllowUsingPivotTables:=True

End Sub

Answer 1

Every cell on excel is Locked by default and after protecting a workbook, you won't be able to edit the cells unless you unlock them beforehand.

You aren't able to unlock the cells, even using VBA code, if the sheet is protected. So if you want to use code to unlock some cells, you have to unprotect the workbook/worksheet first.

Please try my code:

Sub UnlockCells()

Sheet1.Unprotect
Sheet1.Range("A1", "B6").Locked = False 'Unlock the range A1 to B6
Sheet1.Cells(6, 6).Locked = False 'Unlock the cell F6
Sheet1.Protect

End Sub