how to have unlocked cells on a protected VBA created worksheet

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

---

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

---

This may be a bit late ...but I hope it helps here are the steps to do:

1. Lock the sheet under consideration
2. View Code to create a private Subroutine(Right Click Sheet --> View Code --> Select the 'Microsoft Excel Objects' corresponding to this Sheet)

3. Paste this code :

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim ws As Worksheet
   Dim inputRange As Range
   
   
   Set ws = Worksheets("WorkSheetName")
   'tell this sub to unprotect only these cells
   Set inputRange = Range("I5,I7,I11")
   
   
   ' If the selected cell is not in the range keep the sheet locked
   If Intersect(Target, inputRange) Is Nothing Then
   'else unprotect the sheet by providing password 
   '(same as the one that was used to protect this sheet)
   Else
   
       ws.Unprotect Password:="password"
       Target.Locked = False
       ws.Protect Password:="password"
   
   End If
   
   End Sub