VBA used to hide multiple columns works first few times but then it doesn't

[jacqu]

Long story short, I have specific columns on the right side of my worksheet that will 'show' or 'hide' depending on input from the left side of the worksheet. If it was just me, I would do this manually and not bat an eye, but I give this worksheet to a bunch of people who think the only way to enter a formula is by clicking the Sigma button.

The Target.Address of the first 3 If/Else statements refer to cells that are drop down lists and the statements cause the columns to hide/show as intended. My problem lies with the last 3 If/Else Statements of the Sub. Basically, I want columns S, Y, and AE to be visible only when 2 different drop down lists are in a specific combination. I couldn't figure out how to do that using VBA alone so I created "helper cells" using a formula in S3, Y3, and AE3; the formula in S3 looks like this: =IF(OR(AND($M$3="Yes",$L$5=""),$M$3="No",$M$3=""),"X","")
The formulas in Y3 and AE3 are similar, just $L$5 being replaced with $M$5 and $N$5 respectively. I think the problem is that I'm telling it to look for a value of "X" and it sees the formula. I think Indirect needs to be used in some way so that it looks at the result of the formula instead of the formula itself, but I have yet to find how to do that. Any ideas on how to fix my issue?

*Side Note* I feel like my code could probably be simplified, but I only know enough VBA to be dangerous; what I have was pieced together off many, many searches on the subject. If someone can simplify it, by all means, please and thank you.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$L$5" Then
        If Target.Value = "" Then
            Application.Range("P:R,T:U").Select
            Application.Selection.EntireColumn.Hidden = True
        Else
            Application.Range("P:R,T:U").Select
            Application.Selection.EntireColumn.Hidden = False
        End If
    End If


    If Target.Address = "$M$5" Then
        If Target.Value = "" Then
            Application.Range("V:X,Z:AA").Select
            Application.Selection.EntireColumn.Hidden = True
        Else
            Application.Range("V:X,Z:AA").Select
            Application.Selection.EntireColumn.Hidden = False
        End If
    End If


    If Target.Address = "$N$5" Then
        If Target.Value = "" Then
            Application.Range("AB:AD,AF:AG").Select
            Application.Selection.EntireColumn.Hidden = True
        Else
            Application.Range("AB:AD,AF:AG").Select
            Application.Selection.EntireColumn.Hidden = False
        End If
    End If
    
    If Target.Address = "$S$3" Then
        If Target.Value = "X" Then
            Application.Range("S:S").Select
            Application.Selection.EntireColumn.Hidden = True
        Else
            Application.Range("S:S").Select
            Application.Selection.EntireColumn.Hidden = False
        End If
    End If
    
    If Target.Address = "$Y$3" Then
        If Target.Value = "X" Then
            Application.Range("Y:Y").Select
            Application.Selection.EntireColumn.Hidden = True
        Else
            Application.Range("Y:Y").Select
            Application.Selection.EntireColumn.Hidden = False
        End If
    End If
    
    If Target.Address = "$AE$3" Then
        If Target.Value = "X" Then
            Application.Range("AE:AE").Select
            Application.Selection.EntireColumn.Hidden = True
        Else
            Application.Range("AE:AE").Select
            Application.Selection.EntireColumn.Hidden = False
        End If
    End If
    
End Sub

 

[DanteAmor]

The change event is not activated when the result of a formula changes. The value of the cell is not changing the formula remains the same.

Then you must code the formula and check if it is true.

Try this

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("M3, L5, M5, N5")) Is Nothing Then
    
        If Target.Value = "" Then hid = True Else hid = False
        Select Case Target.Address
            Case "$L$5": rng = "P:R,T:U"
            Case "$M$5": rng = "V:X,Z:AA"
            Case "$N$5": rng = "AB:AD,AF:AG"
        End Select
        If rng <> "" Then Range(rng).EntireColumn.Hidden = hid
        
        If (Range("M3") = "Yes" And Range("L5") = "") Or Range("M3") = "No" Or Range("M3") = "" Then
            Application.Range("S:S").EntireColumn.Hidden = True
        Else
            Application.Range("S:S").EntireColumn.Hidden = False
        End If
        If (Range("M3") = "Yes" And Range("M5") = "") Or Range("M3") = "No" Or Range("M3") = "" Then
            Application.Range("Y:Y").EntireColumn.Hidden = True
        Else
            Application.Range("Y:Y").EntireColumn.Hidden = False
        End If
        If (Range("M3") = "Yes" And Range("N5") = "") Or Range("M3") = "No" Or Range("M3") = "" Then
            Application.Range("AE:AE").EntireColumn.Hidden = True
        Else
            Application.Range("AE:AE").EntireColumn.Hidden = False
        End If
    
    End If
    
End Sub

 

[jacqu]

Thank you very much, the code works like charm! It took me a few minutes, but I managed to convert this code to work with another Sheet in the same book that I wanted to do something similar with.

 

[jacqu]

Ok, I played around with it a bit more and I'm wondering if there's a way to correct for clearing multiple cells at the same time?

So let's say I use the Delete key to clear cells L5, M5, and N5 simultaneously, I understand that the Target.Count would need to be increased, but just changing this causes an error code if I try to delete the contents of multiple cells. The error code problem is fixed by using IsEmpty(Target.Value) instead of Target.Value = "", but the Select Case statements don't work (The If statements at the end that are dependent on M3 do still work).

I'm assuming it's because either rng <> "" OR possibly because of the Not Intersect statement (I tried but I don't quite understand what that line accomplishes) OR more likely because even though I have more than 1 cell selected, my target is only the first cell selected?

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[jacqu]

I played around with it a bit more and I'm wondering if there's a way to correct for clearing multiple cells at the same time?

So let's say I use the Delete key to clear cells L5, M5, and N5 simultaneously, I understand that the Target.Count would need to be increased, but just changing this causes an error code if I try to delete the contents of multiple cells. The error code problem is fixed by using IsEmpty(Target.Value) instead of Target.Value = "", but the Select Case statements don't work (The If statements at the end that are dependent on M3 do still work).

I'm assuming it's because either rng <> "" OR possibly because of the Not Intersect statement (I tried but I don't quite understand what that line accomplishes) OR more likely because even though I have more than 1 cell selected, my target is only the first cell selected?

 

[DanteAmor]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Count > 4 Then Exit Sub
    
    If Not Intersect(Target, Range("M3, L5, M5, N5")) Is Nothing Then
    
        For Each wCell In Target
            If wCell.Value = "" Then hid = True Else hid = False
            Select Case wCell.Address
                Case "$L$5": rng = "P:R,T:U"
                Case "$M$5": rng = "V:X,Z:AA"
                Case "$N$5": rng = "AB:AD,AF:AG"
            End Select
            If rng <> "" Then Range(rng).EntireColumn.Hidden = hid
            
            If (Range("M3") = "Yes" And Range("L5") = "") Or Range("M3") = "No" Or Range("M3") = "" Then
                Application.Range("S:S").EntireColumn.Hidden = True
            Else
                Application.Range("S:S").EntireColumn.Hidden = False
            End If
            If (Range("M3") = "Yes" And Range("M5") = "") Or Range("M3") = "No" Or Range("M3") = "" Then
                Application.Range("Y:Y").EntireColumn.Hidden = True
            Else
                Application.Range("Y:Y").EntireColumn.Hidden = False
            End If
            If (Range("M3") = "Yes" And Range("N5") = "") Or Range("M3") = "No" Or Range("M3") = "" Then
                Application.Range("AE:AE").EntireColumn.Hidden = True
            Else
                Application.Range("AE:AE").EntireColumn.Hidden = False
            End If
        Next
    End If
    
End Sub

 

[jacqu]

This worked wonderfully! Sorry it took me so long to get back to you. Thanks again!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.