|
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 09:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com