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

 

[My Aswer Is This]

So do you want the script to run automatically as you change a particular cell value.
Or run when you press a button to run the script?

 

[Alm5423]

I'd like it to run automatically when the cell value is changed. Right now, I have it set it up as a Private Sub Worksheet_Change(ByVal Target As Range) (which might be incorrect).

But the second piece of the code (starting with ElseIf) was working fine until I added the N27 piece.

 

[My Aswer Is This]

I'd like it to run automatically when the cell value is changed. Right now, I have it set it up as a Private Sub Worksheet_Change(ByVal Target As Range) (which might be incorrect).

But the second piece of the code (starting with ElseIf) was working fine until I added the N27 piece.

Show me the entire code

 

[Alm5423]

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


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
End If
End Sub

The first section doesn't apply to my question, it's just the second part.

 

[My Aswer Is This]

I do not understand this:
The first section doesn't apply to my question, it's just the second part.

Is the first part a part of the script?
If not why do you have it here?

To write the entire script I need to know all of what you want.
Do not assume since one part works and another part does not work that I do not need to know all of what you need.

The way your showing the first part of the script will run when you change any cell on the entire sheet.

 

[Alm5423]

Yes, it's part of the script, it's just addressing another issue on the form that I didn't mention above.

 

[My Aswer Is This]

To many if and or statements here:
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[johnnyL]

To many if and or statements here:
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

I think this should equate to same thing:

Private Sub Worksheet_Change(ByVal Target As Range)
'
    Dim celltxt As String, celltxt2 As String
'
    celltxt = ActiveSheet.Range("E33").Text
    celltxt2 = ActiveSheet.Range("N33").Text
'
'---------------------------------------------------------------------------------------------------
'
    If InStr(1, celltxt, "Expat", vbTextCompare) Or InStr(1, celltxt2, "Expat", vbTextCompare) Then
        Rows("34:34").EntireRow.Hidden = False
    Else
        Rows("34:34").EntireRow.Hidden = True
    End If
'
'---------------------------------------------------------------------------------------------------
'
    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" Then
        Range("60:63").EntireRow.Hidden = False
    ElseIf Range("E28").Value = "Hrly" And Range("N28").Value = "EX" Then
        Range("60:63").EntireRow.Hidden = False
    Else
        Range("60:63").EntireRow.Hidden = True
    End If
End Sub

 

[Alex Blakenburg]

@johnnyL, the first line is going to stop it from getting to the 2nd line
If Range("N27").Value <> ""
Elseif ..... Range("N27").Value = "CE"