Multiple change events in one worksheet based on picklist values

[DMack15]

I am new to <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> and am struggling with the code for multiple Worksheet Change Events for one worksheet. I was able to get them to work solo....but not together. The example you provided in this has confused me and I'm hoping that you'll be able to help.

Basically, I have two picklists. The first change event is simple....just unhide row 7 if "Custom Project" is chosen in the picklist in "D6".

The second change event is based on two values in one picklist. If "Custom" is chosen in the picklist in "D20" then unhide row 21; If "Clone Existing Email Program" is selected then unhide row 22.

Additionally, I need to figure out how to start with these rows automatically hidden AND automatically update if the user changes their mind and goes to a different selection in the picklist.

Below is what I was using to get the function to work alone...can anywone help be combine the code into one Worksheet Change event?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "D6" Then
Select Case Target.Value
Case "Custom Project": Rows("7").Hidden = False
Case "Email": Rows("7").Hidden = True
Case "Landing Form Page": Rows("7").Hidden = True
Case "Fulfillment/Thank You Page": Rows("7").Hidden = True
Case "List Upload": Rows("7").Hidden = True
Case "Tracking URLs": Rows("7").Hidden = True
Case "SFDC Campaign ID": Rows("7").Hidden = True
Case "Reporting": Rows("7").Hidden = True
End Select
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "D20" Then
Select Case Target.Value
Case "Custom": Rows("21").Hidden = False
Case "Offer-Based Email": Rows("21").Hidden = True
Case "Event-Based Email": Rows("21").Hidden = True
Case "Newsletter": Rows("20").Hidden = True
Case "Transactional Email (Confirmation, Reminder, Trigger)": Rows("21").Hidden = True
Case "[Co-Brand] Offer-Based Email": Rows("21").Hidden = True
Case "[Co-Brand] Event-Based Email": Rows("21").Hidden = True
Case "[Co-Brand] Newsletter Template": Rows("21").Hidden = True
Case "[Co-Brand] Transactional Email (Confirmation, Reminder, Trigger)": Rows("21").Hidden = True
Case "Clone Existing Email Program": Rows("21").Hidden = True
End SelectEnd If
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "D20" Then
Select Case Target.Value
Case "Custom": Rows("22").Hidden = True
Case "Offer-Based Email": Rows("22").Hidden = True
Case "Event-Based Email": Rows("22").Hidden = True
Case "Newsletter": Rows("22").Hidden = True
Case "Transactional Email (Confirmation, Reminder, Trigger)": Rows("22").Hidden = True
Case "[Co-Brand] Offer-Based Email": Rows("22").Hidden = True
Case "[Co-Brand] Event-Based Email": Rows("22").Hidden = True
Case "[Co-Brand] Newsletter Template": Rows("22").Hidden = True
Case "[Co-Brand] Transactional Email (Confirmation, Reminder, Trigger)": Rows("22").Hidden = True
Case "Clone Existing Email Program": Rows("22").Hidden = False
End Select
End If
End Sub

 

[AlphaFrog]

Try this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]Select[/color] [color=darkblue]Case[/color] Target.Address(False, False)
        [color=darkblue]Case[/color] "D6"
            Rows(7).Hidden = Target.Value <> "Custom Project"
        [color=darkblue]Case[/color] "D20"
            Rows(21).Hidden = Target.Value <> "Custom"
            Rows(22).Hidden = Target.Value <> "Clone Existing Email Program"
    [color=darkblue]End[/color] [color=darkblue]Select[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[DMack15]

Wow - that's it?

I was over complicating things. This is working great. Thank you!

 

[AlphaFrog]

You're welcome. Thanks for the feedback.

For future reference, see my signature block below about the use of CODE tags. It makes reading the VBA code within your thread much easier.