• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1323
  • Last Modified:

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
0
mike637
Asked:
mike637
  • 3
  • 2
1 Solution
 
redmondbCommented:
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

0
 
mike637Author Commented:
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
0
 
redmondbCommented:
Michael,

My apologies, I forgot about that. In the code below I included a couple of lines to set the specified cells' colour to help in testing - just delete the two lines when you're content.

The code is...
Option Explicit

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

Application.EnableEvents = False
     
Const FirstRow As Long = 11
Const lastRow As Long = 13
 
Const FirstCol As String = "A"
Const LastCol As String = "A"

xRange = ("A??, C??:G??, I??:V??, X??")
  
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
        Range(WorksheetFunction.Substitute(xRange, "??", HiddenRow)).Locked = False
        Range(WorksheetFunction.Substitute(xRange, "??", HiddenRow)).Interior.Color = 65535
    Else
         Rows(HiddenRow).EntireRow.Hidden = True
         
         For Each cel In Rows(HiddenRow)
            If Not IsNumeric(cel) Then cel.ClearContents
         Next cel
         Range(WorksheetFunction.Substitute(xRange, "??", HiddenRow)).Locked = True
         Range(WorksheetFunction.Substitute(xRange, "??", HiddenRow)).Interior.Color = 15773696
   End If
   
Next HiddenRow

Application.EnableEvents = True

Set rowrange = Nothing

End Sub

Open in new window

Regards,
Brian.
0
 
mike637Author Commented:
Thanks again for saving the day for me Brian.

Have a great rest of your day!

Michael
0
 
redmondbCommented:
Thanks, mike637, glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now