Excel VBA - lock all cells in hidden row or unlock cell if row unhidden
Posted on 2013-01-29
I am some code that worked fine - but I need to adjust it to lock the cells in the hidden row and unlock them if the row becomes unhidden.
Maybe I overcomplicated the routine, not sure, and now I am also stuck on locking and unlocking the cells.
I had to cancel enable events to stop the worksheet change routine.
The columns in the rows to lock or unlock will always be ("A, C:G, I:V,X")
Here is my code:
Private Sub CheckRows()
Dim HiddenRow&, rowrange As range, RowRangeValue As Single
Application.EnableEvents = False
Const FirstRow As Long = 11
Const lastRow As Long = 13
Const FirstCol As String = "A"
Const LastCol As String = "A"
For HiddenRow = FirstRow To lastRow
Set rowrange = range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
RowRangeValue = Application.Sum(rowrange.Value)
If RowRangeValue > "0" Then
Rows(HiddenRow).EntireRow.Hidden = False
' (This is where is need to unlock cells in the row of the denoted columns)
Rows(HiddenRow).EntireRow.Hidden = True
For Each cel In Rows(HiddenRow)
If Not IsNumeric(cel) Then cel.ClearContents
' This is where I need to lock cells in the row of the denoted columns)
Application.EnableEvents = True
Set rowrange = Nothing