Link to home
Start Free TrialLog in
Avatar of mike637
mike637Flag for United States of America

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.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)
                                           
        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
Avatar of redmondb
redmondb
Flag of Afghanistan image

Hi, mike637.

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.
Option Explicit

Private Sub CheckRows()
Dim HiddenRow&, rowrange As Range, RowRangeValue As Single
Dim cel As Range

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
         rowrange.EntireRow.Locked = 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
        rowrange.EntireRow.Locked = True
         ' 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

Open in new window

Avatar of mike637

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
ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mike637

ASKER

Thanks again for saving the day for me Brian.

Have a great rest of your day!

Michael
Thanks, mike637, glad to help!