VBA to Hide/Unhide Rows

[Alm5423]

Hi Everyone,

I'm currently building a form where I have a number of approval lines at the bottom of a spreadsheet. In most cases, rows 60-63 need to be hidden from view but there are a select number of circumstances where I need to unhide them.

For example, I need to write a code that allows me to unhide rows 60-61 if cell N27 on my spreadsheet is filled in....but then I also need to unhide rows 60-63 if any of the following conditions are met:

Cell B36=True
Cell B46=True
Cell N27="CE"
Cell E28 ="Hrly" And Cell N28 "Ex"

In all other cases not mentioned above, rows 60-63 should remain hidden. Does anyone know how to write a code for something like that? I started to write down some lines below but couldn't figure out how to correctly integrate the N27 piece. I'm fairly new to the VBA syntax.

If Range("N27").Value <> "" Then
Range("60:61").EntireRow.Hidden = False
ElseIf Range("B36").Value = True Or Range("B46").Value = True Or Range("N27").Value = "CE" Or Range("E28").Value = "Hrly" And Range("N28").Value = "Ex" Then
Range("60:63").EntireRow.Hidden = False
Else: Range("60:63").EntireRow.Hidden = True

 

[johnnyL]

Don't blame me, blame the original Poster.

And I don't think the first line would stop it from getting to the second line, Just the last 'Or' in the second line would never be executed. Right?

The second line will execute if the first line, Range("N27").Value = "", Unless I have had too many spirits.

 

[My Aswer Is This]

With the original code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim celltxt As String
Dim celltxt2 As String
celltxt = ActiveSheet.Range("E33").Text
celltxt2 = ActiveSheet.Range("N33").Text
If InStr(1, celltxt, "Expat", vbTextCompare) Then
Rows("34:34").EntireRow.Hidden = False
ElseIf InStr(1, celltxt2, "Expat", vbTextCompare) Then
Rows("34:34").EntireRow.Hidden = False
Else: Rows("34:34").EntireRow.Hidden = True
End If

The script will run any time any value on the sheet is change Unless I have had too many spirits.

There is nothing here restricting it to a certain range

 

[Alex Blakenburg]

Give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim celltxt As String, celltxt2 As String
    
    ' XXX Calculations won't trigger the change event so the intersect
    ' XXX only needs to include the input cells that are the drivers
    If Intersect(Target, Range("E33, N33, B36, B46,N27,E28,N28")) Then
        
        celltxt = ActiveSheet.Range("E33").Text
        celltxt2 = ActiveSheet.Range("N33").Text
        If InStr(1, celltxt, "Expat", vbTextCompare) Then
            Rows("34:34").EntireRow.Hidden = False
        ElseIf InStr(1, celltxt2, "Expat", vbTextCompare) Then
            Rows("34:34").EntireRow.Hidden = False
        Else
            Rows("34:34").EntireRow.Hidden = True
        End If
        
        Range("60:63").EntireRow.Hidden = True
        
        If Range("B36").Value = True Or _
                Range("B46").Value = True Or _
                Range("N27").Value = "CE" Or _
                (Range("E28").Value = "Hrly" And Range("N28").Value = "EX") Then
            
            Range("60:63").EntireRow.Hidden = False
        ElseIf Range("N27").Value <> "" Then
            Range("60:61").EntireRow.Hidden = False
        End If
    
    End If
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

 

[Alex Blakenburg]

My first IF Intersect is incorrect please replace it wit this:-

    If Not Intersect(Target, Range("E33,N33,B36,B46,N27,E28,N28")) Is Nothing Then