hide / show rows based on two drop down selectiosn

M

Maritza

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!
 
R

Ron de Bruin

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
 
M

Maritza

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
 
R

Ron de Bruin

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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top