| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Showing or Hiding rows based on data validation list
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" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Showing or Hiding rows based on data validation list
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" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Showing or Hiding rows based on data validation list
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" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Showing or Hiding rows based on data validation list
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" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Validation List (Drop Down) Not Showing | Excel Discussion (Misc queries) | |||
| Hiding/Showing Rows on Cell H2 Change | Excel Discussion (Misc queries) | |||
| Data Validation List Not Showing | Excel Discussion (Misc queries) | |||
| hiding or showing rows based on a cell value | Excel Discussion (Misc queries) | |||
| Data Validation - Allow List - In-cell Dropdown not showing up | Excel Programming | |||
Linear Mode
