Solved

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

Posted on 2013-01-29
5
1,007 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks again for saving the day for me Brian.

Have a great rest of your day!

Michael
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, mike637, glad to help!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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 briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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

11 Experts available now in Live!

Get 1:1 Help Now