Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide / show rows based on two drop down selectiosn
I really need help again. Now I have two drop downs and rows need to be
hidden or shown based on both selections. The following works for one. Can I add a second target? Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D7"), Target) Is Nothing Then Range("A29:A47").EntireRow.Hidden = False If Target.Value = "Refund" Then Range("a29:a32,A39:A40").EntireRow.Hidden = True ElseIf Target.Value = "Write off" Then Range("a33:a46").EntireRow.Hidden = True Else Range("A29:a32,a41:a46").EntireRow.Hidden = True End If End If End Sub Thanks! -- Maritza |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide / show rows based on two drop down selectiosn
Hi Maritza
Repeat this block for your second cell If Not Application.Intersect(Range("D7"), Target) Is Nothing Then Range("A29:A47").EntireRow.Hidden = False If Target.Value = "Refund" Then Range("a29:a32,A39:A40").EntireRow.Hidden = True ElseIf Target.Value = "Write off" Then Range("a33:a46").EntireRow.Hidden = True Else Range("A29:a32,a41:a46").EntireRow.Hidden = True End If End If -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Maritza" wrote in message ... I really need help again. Now I have two drop downs and rows need to be hidden or shown based on both selections. The following works for one. Can I add a second target? Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D7"), Target) Is Nothing Then Range("A29:A47").EntireRow.Hidden = False If Target.Value = "Refund" Then Range("a29:a32,A39:A40").EntireRow.Hidden = True ElseIf Target.Value = "Write off" Then Range("a33:a46").EntireRow.Hidden = True Else Range("A29:a32,a41:a46").EntireRow.Hidden = True End If End If End Sub Thanks! -- Maritza |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide / show rows based on two drop down selectiosn
When I do the following the drop downs trigger the hide/show independently. I
need them to work be more specific. Like the following word explanation. When drop down A (D4) equals Valid Charge Credit, then Range("A29:a32,a41:a46").EntireRow.Hidden = True no matter what other selection is made except if drop down A (D4) is valid charge AND drop down B (D7) is write off, then Range("a33:a46").EntireRow.Hidden = True if drop down A does not equal valid charge credit, then proceed to the next set (drop down B (D7)) criteria Thanks! Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D4"), Target) Is Nothing Then Range("A29:A47").EntireRow.Hidden = False If Target.Value = "Valid Charge Credit" Then Range("A29:a32,a41:a46").EntireRow.Hidden = True Else Range("A29:a32,a41:a46").EntireRow.Hidden = True End If End If If Not Application.Intersect(Range("D7"), Target) Is Nothing Then Range("A29:A47").EntireRow.Hidden = False If Target.Value = "Refund" Then Range("a29:a32,A39:A40").EntireRow.Hidden = True ElseIf Target.Value = "Write off" Then Range("a33:a46").EntireRow.Hidden = True Else Range("A29:a32,a41:a46").EntireRow.Hidden = True End If End If -- Maritza "Ron de Bruin" wrote: Hi Maritza Repeat this block for your second cell If Not Application.Intersect(Range("D7"), Target) Is Nothing Then Range("A29:A47").EntireRow.Hidden = False If Target.Value = "Refund" Then Range("a29:a32,A39:A40").EntireRow.Hidden = True ElseIf Target.Value = "Write off" Then Range("a33:a46").EntireRow.Hidden = True Else Range("A29:a32,a41:a46").EntireRow.Hidden = True End If End If -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Maritza" wrote in message ... I really need help again. Now I have two drop downs and rows need to be hidden or shown based on both selections. The following works for one. Can I add a second target? Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D7"), Target) Is Nothing Then Range("A29:A47").EntireRow.Hidden = False If Target.Value = "Refund" Then Range("a29:a32,A39:A40").EntireRow.Hidden = True ElseIf Target.Value = "Write off" Then Range("a33:a46").EntireRow.Hidden = True Else Range("A29:a32,a41:a46").EntireRow.Hidden = True End If End If End Sub Thanks! -- Maritza |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
hide / show rows based on two drop down selectiosn
Hi Maritza
You can add a If statement to check the value of the other dropdown if you change the first one and do what you want then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Maritza" wrote in message ... When I do the following the drop downs trigger the hide/show independently. I need them to work be more specific. Like the following word explanation. When drop down A (D4) equals Valid Charge Credit, then Range("A29:a32,a41:a46").EntireRow.Hidden = True no matter what other selection is made except if drop down A (D4) is valid charge AND drop down B (D7) is write off, then Range("a33:a46").EntireRow.Hidden = True if drop down A does not equal valid charge credit, then proceed to the next set (drop down B (D7)) criteria Thanks! Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D4"), Target) Is Nothing Then Range("A29:A47").EntireRow.Hidden = False If Target.Value = "Valid Charge Credit" Then Range("A29:a32,a41:a46").EntireRow.Hidden = True Else Range("A29:a32,a41:a46").EntireRow.Hidden = True End If End If If Not Application.Intersect(Range("D7"), Target) Is Nothing Then Range("A29:A47").EntireRow.Hidden = False If Target.Value = "Refund" Then Range("a29:a32,A39:A40").EntireRow.Hidden = True ElseIf Target.Value = "Write off" Then Range("a33:a46").EntireRow.Hidden = True Else Range("A29:a32,a41:a46").EntireRow.Hidden = True End If End If -- Maritza "Ron de Bruin" wrote: Hi Maritza Repeat this block for your second cell If Not Application.Intersect(Range("D7"), Target) Is Nothing Then Range("A29:A47").EntireRow.Hidden = False If Target.Value = "Refund" Then Range("a29:a32,A39:A40").EntireRow.Hidden = True ElseIf Target.Value = "Write off" Then Range("a33:a46").EntireRow.Hidden = True Else Range("A29:a32,a41:a46").EntireRow.Hidden = True End If End If -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Maritza" wrote in message ... I really need help again. Now I have two drop downs and rows need to be hidden or shown based on both selections. The following works for one. Can I add a second target? Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D7"), Target) Is Nothing Then Range("A29:A47").EntireRow.Hidden = False If Target.Value = "Refund" Then Range("a29:a32,A39:A40").EntireRow.Hidden = True ElseIf Target.Value = "Write off" Then Range("a33:a46").EntireRow.Hidden = True Else Range("A29:a32,a41:a46").EntireRow.Hidden = True End If End If End Sub Thanks! -- Maritza |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide/shows raw based on drop down menue | Excel Programming | |||
Hide & Show Rows based on Check Boxes | Excel Discussion (Misc queries) | |||
Hide row(s) based on drop down selection | Excel Discussion (Misc queries) | |||
Hide/Show a row based on data entry in another row | Excel Programming | |||
Hide Rows - copy and paste only rows that show | Excel Worksheet Functions |