Showing or Hiding rows based on data validation list

[Guest]

I am trying to create show different rows to an "order" sheet based on the
answer from a data validation list. So, for example, if the payment type
chosen for this order is "Credit Card" I would like rows 16,17 to pop up,
and if "Check" is chosen i want rows 18,19 to pop up, and so on. And
conversly, if "nothing" is chosen, all the rows are hidden.

I've successfully used something I found on here to show or hide rows based
on a checkbox:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Range(Rows(10), Rows(11)).EntireRow.Hidden = False
ElseIf CheckBox1 = False Then
Range(Rows(10), Rows(11)).EntireRow.Hidden = True
End If
End Sub

This works great,, but I would rather use either a simple data validation
list or a combobox. I tried to apply this idea to the content in a cell like
so:

If A15 = "" Then
Range(Rows(10), Rows(11)).EntireRow.Hidden = True
If A15 = "CC" Then
Range(Rows(10), Rows(11)).EntireRow.Hidden = False

but to no avail. I am, like many, such a n00b. Any push in the right
direction would be most appreciated.

BTW, the content here has been invaluable. Whew, ya'll are making me look
good. THX

 

[Rick Hansen]

Good Morning Magnet Peddler,

Here is alittle bit of code that might get you started in the right
direct with your project.. Add the following code to worksheet_Change()
event sub. Modify where needed...

enjoy, Rick



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$15" Then
If Target.Value = "" Then
Range(Rows(10), Rows(11)).EntireRow.Hidden = True
End If
If Target.Value = "CC" Then
Range(Rows(10), Rows(11)).EntireRow.Hidden = False
End If
End If

End Sub

 

[Guest]

Afternoon Rick,

Thanks for the quick response and push in the right direction. This is
where i have ended up so far(a little more added):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$15" Then
If Target.Value = "" Then
Range(Rows(16), Rows(23)).EntireRow.Hidden = True
End If
If Target.Value = "Check" Then
Range(Rows(16), Rows(17)).EntireRow.Hidden = False
Range(Rows(18), Rows(23)).EntireRow.Hidden = True
End If
If Target.Value = "CC" Then
Range(Rows(16), Rows(17)).EntireRow.Hidden = True
Range(Rows(18), Rows(19)).EntireRow.Hidden = False
Range(Rows(20), Rows(23)).EntireRow.Hidden = True
End If
If Target.Value = "Cash" Then
Range(Rows(20), Rows(21)).EntireRow.Hidden = False
Range(Rows(16), Rows(19)).EntireRow.Hidden = True
Range(Rows(22), Rows(23)).EntireRow.Hidden = True
End If
If Target.Value = "Monopoly Money " Then
Range(Rows(22), Rows(23)).EntireRow.Hidden = False
Range(Rows(16), Rows(21)).EntireRow.Hidden = True
End If

End If

End Sub

It is working well, with the exception of making the rows disappear after
deleting the content of the validation list making it "" again. The last
rows shown remain. My Validation list contains a row with "" in it. Upon
choosing that "" option, I had hoped it would hide once again, but alas no.
I've changed the order around, played with using an

ElseIf Target.Value = "" Then
Range(Rows(16), Rows(23)).EntireRow.Hidden = True

in different places etc...again no. I am not sure how the order of the code
effects its function. That is something to research for me. Again, thx for
the great help, I'll become useful in excel yet . Any help to further
would once again make me look like THE MAN.

 

[Guest]

Ok, I see something at least. If I use the delete button to empty the data
in the validation cell A15 it will not work, but if I delete the value by
hitting a check mark in the formula bar while in A15 with no text entered it
works.

 

[Guest]

I know i can use a simple character like "-" instead of an empty cell in my
validation list, but my curiosity is a strong force here in helping me
understand how vba works.