PDA

View Full Version : Solved: Row to hide on drop down selection



Phelony
04-23-2009, 08:20 AM
:help Once more I knock on your door seeking VBA based wisdom! :hi:

I've got a drop down menu that is supposed to trigger sets of rows to hide when different selections are made. :thumb

However, after several attempts, I've made it either get rid of all the rows, not give them back or just stare blankly back at me hoping I'll go away and leave it alone. :banghead:

From the below, when I select "client" it should hide rows 3,5,7 and when I select "trust" rows 3,5,7 should come back and rows 2,4,6 should hide.

There will be instances when the same row is required for multiple selections in the full version, but for now, could someone please just let me know how I've screwed this up :eek: and how I can fix it? :cloud9:

Private Sub Worksheet_Change(ByVal Target As range)

If Target.Address = "$A$1" Then
If Target.Value = Client Then
Rows(3).EntireRow.Hidden = True
Rows(5).EntireRow.Hidden = True
Rows(7).EntireRow.Hidden = True
Else
Rows(2).EntireRow.Hidden = False
Rows(4).EntireRow.Hidden = False
Rows(6).EntireRow.Hidden = False
End If
If Target.Value = Trust Then
Rows(2).EntireRow.Hidden = True
Rows(4).EntireRow.Hidden = True
Rows(6).EntireRow.Hidden = True
Else

Rows(3).EntireRow.Hidden = False
Rows(5).EntireRow.Hidden = False
Rows(7).EntireRow.Hidden = False
End If
End If
End Sub

Thanks

Phel x :rofl:

mdmackillop
04-23-2009, 11:00 AM
I prefer Select to multiple Ifs

Option Compare Text 'Ignore case

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Select Case Target.Value
Case "Client"
Cells.Rows.Hidden = False
Rows(3).EntireRow.Hidden = True
Rows(5).EntireRow.Hidden = True
Rows(7).EntireRow.Hidden = True
Case "Trust"
Cells.Rows.Hidden = False
Rows(2).EntireRow.Hidden = True
Rows(4).EntireRow.Hidden = True
Rows(6).EntireRow.Hidden = True
End Select
End Sub

p45cal
04-23-2009, 11:41 AM
a variation on a theme:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("2:2,4:4,6:6").EntireRow.Hidden = Target = "Trust"
Range("3:3,5:5,7:7").EntireRow.Hidden = Target = "Client"
End If
End Sub

mdmackillop
04-23-2009, 11:43 AM
Neat, but you forgot the Unhide line :thumb

p45cal
04-23-2009, 11:51 AM
no I didn't

mdmackillop
04-23-2009, 12:09 PM
:stars: Even neater then!

Phelony
04-24-2009, 01:34 AM
Md, code works amazingly, massive amendments made for the annoying large sheet I've applied it to, but the one line unhide is the genius I was lacking! :thumb

P45, can't get yours to work....:wot

All the same, damsel in distress is no longer distressed! :bow:

Thank you so much :clap:

Phel x

mdmackillop
04-24-2009, 09:01 AM
Hi Phelony
re P45's code. It is case specific. Try adding "Option Compare Text" before the Event code.