Assuming you have a worksheet of codename Sheet2 for the logging, here is an option.
Note, instead of creating 1000 check-boxes, I've just used a double click event on the range. A10 down 1000 rows.
Please modify to suit & add some error handling & tweaks as required.
Locking worksheets can create complications for users, navigation, filtering, printing, whatever & you can tie yourself in knots trying to cover everything you need.
Anyway, this will give you some ideas & I'll leave it to you to change/develop further.
Rich (BB code):
'The next few lines define the range of cells that show "LOCKED" or "UNLOCKED" and control what is blocked
Const mlFIRST_DATA_COL As Long = 1 '1 means column "A"
Const mlFIRST_DATA_ROW As Long = 10 '10 means the first data row is row 10
Const mlHOW_MANY_DATA_ROWS As Long = 1000 '1000 means there are 1000 rows of data
Const mlHOW_MANY_COLUMNS_ARE_LOCKED As Long = 66 '66 is for the 66 columns on the immediate RHS of "FIRST_DATA_COL"
Private Sub Worksheet_Activate()
Me.Cells(mlFIRST_DATA_ROW, mlFIRST_DATA_COL).Resize(mlHOW_MANY_DATA_ROWS).Name = "rngSwitchCells"
Me.Cells(mlFIRST_DATA_ROW, mlFIRST_DATA_COL).Resize(mlHOW_MANY_DATA_ROWS).Offset(, 1).Resize(, mlHOW_MANY_COLUMNS_ARE_LOCKED).Name = "rngLogChanges"
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const sWORD_LOCKED As String = "LOCKED"
Const sWORD_UNLOCKED As String = "UNLOCKED"
Const sPASSWORD As String = "accountant123"
'if the cell double clicked is one of the cells that controls the switching between LOCKED & UNLOCKED
If Not Intersect(Target, Me.Range("rngSwitchCells")) Is Nothing Then
Cancel = True
'rngSwitchCells.Locked = False
Me.Protect Password:=sPASSWORD, UserInterfaceOnly:=True
'if cell already says LOCKED
If Target.Value2 = sWORD_LOCKED Then
'Data is currently locked. change to unlocked
Target.Value2 = sWORD_UNLOCKED
Target.Interior.Color = xlNone
Target.Offset(, 1).Resize(mlHOW_MANY_COLUMNS_ARE_LOCKED).Locked = False
Else
'Data was not locked, change it to locked
Target.Value2 = sWORD_LOCKED
Target.Interior.Color = vbYellow
Target.Offset(, 1).Resize(mlHOW_MANY_COLUMNS_ARE_LOCKED).Locked = True
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lNextRow As Long
Dim rngOneCell As Excel.Range
If Not Intersect(Target, Me.Range("rngLogChanges")) Is Nothing Then
For Each rngOneCell In Target.Cells
With Sheet2
lNextRow = .Cells(Sheet2.Rows.Count, 1).End(xlUp).Row + 1
With .Cells(lNextRow, 1)
.Value = Now
.Offset(, 1).Value2 = rngOneCell.Address
.Offset(, 2).Value2 = rngOneCell.Value2
End With
End With
Next rngOneCell
End If
End Sub
