mike637
asked on
Excel VBA - lock all cells in hidden row or unlock cell if row unhidden
Hello Experts,
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.V alue)
If RowRangeValue > "0" Then
Rows(HiddenRow).EntireRow. Hidden = False
' (This is where is need to unlock cells in the row of the denoted columns)
Else
Rows(HiddenRow).EntireRow. Hidden = True
For Each cel In Rows(HiddenRow)
If Not IsNumeric(cel) Then cel.ClearContents
Next cel
' This is where I need to lock cells in the row of the denoted columns)
End If
Next HiddenRow
Application.EnableEvents = True
Set rowrange = Nothing
End Sub
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.V
If RowRangeValue > "0" Then
Rows(HiddenRow).EntireRow.
' (This is where is need to unlock cells in the row of the denoted columns)
Else
Rows(HiddenRow).EntireRow.
For Each cel In Rows(HiddenRow)
If Not IsNumeric(cel) Then cel.ClearContents
Next cel
' This is where I need to lock cells in the row of the denoted columns)
End If
Next HiddenRow
Application.EnableEvents = True
Set rowrange = Nothing
End Sub
ASKER
Hello Brian,
Yes, this routine is called from another where it is unlocked and then relocked.
On your entries you made - on the rowrange.entirerow.locked = true will work on the else statement.
But on the locked = false - I only want the the columns in the rows to unlock to be ("A, C:G, I:V,X"). I am not sure to use range or cells. I tried both, both I think I am calling it wrong.
Michael
Yes, this routine is called from another where it is unlocked and then relocked.
On your entries you made - on the rowrange.entirerow.locked = true will work on the else statement.
But on the locked = false - I only want the the columns in the rows to unlock to be ("A, C:G, I:V,X"). I am not sure to use range or cells. I tried both, both I think I am calling it wrong.
Michael
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again for saving the day for me Brian.
Have a great rest of your day!
Michael
Have a great rest of your day!
Michael
Thanks, mike637, glad to help!
Please see the code below. I assume that you're using sheet protection, so shouldn't there be code to unprotect and then re-protect the sheet?
Regards,
Brian.
Open in new window