VBA code is not working using Private Sub Worksheet_Change Function

Question

I am trying to use selection from cell E73 to hide or display rows on two different tabs. my vba code below only works for one tab and not both. Can you spot where my issue is? Any help is appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Target.CountLarge > 1 Then Exit Sub
Select Case Target.Address(0, 0)
    Case "E73"
        Set rng1 = Sheets("Proposal").Rows("349:403")
        Set rng2 = Sheets("Binder").Rows("350:404")
    Case "E128"
        Set rng1 = Sheets("Proposal").Rows("404:462")
        Set rng2 = Sheets("Binder").Rows("405:463")
End Select
If rng Is Nothing Then Exit Sub

    Select Case Target.Value
        Case "Included"
            rng1.Hidden = False
            rng2.Hidden = False
        Case "Excluded"
            rng1.Hidden = True
            rng2.Hidden = True
    End Select
End Sub

If rng Is Nothing Then Exit Sub... this is always True. You never Set rng = .... You probably would benefit by adding Option Explicit to the top of the module and declaring all variables. — BigBen
– BigBen, Commented Sep 16, 2022 at 16:07
Any way you could show me via example? I am not good with vba codes and just getting familiar. Any help is appreciated to get this code up and running. — Kkoruni
– Kkoruni, Commented Sep 16, 2022 at 16:28

BigBen · Accepted Answer · 2022-09-16 16:33:01Z

1

If rng Is Nothing Then Exit Sub

This is always True, because rng is never Set.

A guess as to what you want:

Select Case Target.Address(0, 0)
    Case "E73"
        Set rng1 = Sheets("Proposal").Rows("349:403")
        Set rng2 = Sheets("Binder").Rows("350:404")
    Case "E128"
        Set rng1 = Sheets("Proposal").Rows("404:462")
        Set rng2 = Sheets("Binder").Rows("405:463")
    Case Else
        Exit Sub
End Select

Select Case Target.Value
    Case "Included"
        rng1.Hidden = False
        rng2.Hidden = False
    Case "Excluded"
        rng1.Hidden = True
        rng2.Hidden = True
    End Select
End Sub

answered Sep 16, 2022 at 16:33

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kkoruni Over a year ago

So instead of using (If rng1 Is Nothing Then Exit Sub) I should change it to (Case Else Exit Sub)?

BigBen Over a year ago

Yes, that's what is proposed. Also note that you have rng, not rng1. Big difference.

Kkoruni Over a year ago

Correct. Typo error. I just tried it and worked like magic. Thank you for all the help BigBen.

BigBen Over a year ago

Ha. Well it's not magic, it's logic. You're welcome!

Collectives™ on Stack Overflow

VBA code is not working using Private Sub Worksheet_Change Function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related