Solved

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

Posted on 2013-01-29
5
1,034 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

896 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now