how to have unlocked cells on a protected VBA created worksheet

1
votes

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
2
excelvba

2 Answers

4
votes

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
2
votes

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