Solved

Excel VBA - lock all cells in hidden row or unlock cell if row unhidden

Posted on 2013-01-29
5
1,050 Views
Last Modified: 2013-01-29
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
Comment
Question by:mike637
  • 3
  • 2
5 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38831520
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
 

Author Comment

by:mike637
ID: 38831597
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
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38832029
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
 

Author Closing Comment

by:mike637
ID: 38832309
Thanks again for saving the day for me Brian.

Have a great rest of your day!

Michael
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38832373
Thanks, mike637, glad to help!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Finding a closest match in Excel 7 49
Excel VBA 4 27
VBA Fill Blanks with text from another cell 6 23
Formula help - MIN 7 17
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question