Showing or Hiding rows based on data validation list

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
--
Murphy''s first law of combat: Incoming fire always has the right of way.

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



"Magnet Peddler" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> Murphy''s first law of combat: Incoming fire always has the right of way.

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.
--
Murphy''''s first law of combat: Incoming fire always has the right of way.


"Rick Hansen" wrote:

> 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
>
>
>
> "Magnet Peddler" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > --
> > Murphy''s first law of combat: Incoming fire always has the right of way.
>
>
>

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.
--
Murphy''''s first law of combat: Incoming fire always has the right of way.


"Magnet Peddler" wrote:

> 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.
> --
> Murphy''''s first law of combat: Incoming fire always has the right of way.
>
>
> "Rick Hansen" wrote:
>
> > 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
> >
> >
> >
> > "Magnet Peddler" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > --
> > > Murphy''s first law of combat: Incoming fire always has the right of way.
> >
> >
> >

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.
--
Murphy''''s first law of combat: Incoming fire always has the right of way.


"Magnet Peddler" wrote:

> 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.
> --
> Murphy''''s first law of combat: Incoming fire always has the right of way.
>
>
> "Magnet Peddler" wrote:
>
> > 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.
> > --
> > Murphy''''s first law of combat: Incoming fire always has the right of way.
> >
> >
> > "Rick Hansen" wrote:
> >
> > > 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
> > >
> > >
> > >
> > > "Magnet Peddler" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > > --
> > > > Murphy''s first law of combat: Incoming fire always has the right of way.
> > >
> > >
> > >