New Workbook_Change Problem

Zamascus · May 11th, 2009, 05:40 PM

Im by no means a programmer - I can figure out enough to get around. That said I have a worksheet with a bunch of different "packages" in it typed into sets of rows of cells. Lets say Package 1 is for Allergy Shots, Package 2 is Allergy Testing, etc.

I have created a list of all the packages on another page and made a dropdown on my main workbook page which is Cell A8. I want to show and hide ranges of rows depending on which package is selected.

The following code works - but the problem is it runs through ALL of the If statements which is really really annoying. Its going to end up being about 75 packages long Can anyone help me avoid this?

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("A8")
On Error GoTo Crap
If rng.Value = "Allergy Shots" Then
[36:56].EntireRow.Hidden = False
Else: [36:56].EntireRow.Hidden = True
End If
If rng.Value = "Allergy Testing" Then
[57:76].EntireRow.Hidden = False
Else: [57:76].EntireRow.Hidden = True
End If
If rng.Value = "Chest X-Ray" Then
[77:96].EntireRow.Hidden = False
[2000:2018].EntireRow.Hidden = False
Else: [77:96].EntireRow.Hidden = True
[2000:2018].EntireRow.Hidden = True
End If
If rng.Value = "Circumcision" Then
[97:111].EntireRow.Hidden = False
Else: [97:111].EntireRow.Hidden = True
End If
If rng.Value = "Colonoscopy" Then
[112:131].EntireRow.Hidden = False
Else: [112:131].EntireRow.Hidden = True
End If
If rng.Value = "Colposcopy" Then
[132:149].EntireRow.Hidden = False
[2000:2018].EntireRow.Hidden = False
Else: [132:149].EntireRow.Hidden = True
[2000:2018].EntireRow.Hidden = True
End If
If rng.Value = "CT Scan Of Abdomen" Then
[165:182].EntireRow.Hidden = False
Else: [165:182].EntireRow.Hidden = True
End If
If rng.Value = "CT Scan Of Chest" Then
[183:201].EntireRow.Hidden = False
Else: [183:201].EntireRow.Hidden = True
End If
If rng.Value = "CT Scan Of Ear, Orbit, Sella Turcica" Then
[202:220].EntireRow.Hidden = False
Else: [202:220].EntireRow.Hidden = True
End If
If rng.Value = "CT Scan Of Extremity - Arm Or Leg" Then
[221:247].EntireRow.Hidden = False
Else: [221:247].EntireRow.Hidden = True
End If
If rng.Value = "CT Scan Of Face, Maxilla" Then
[248:266].EntireRow.Hidden = False
Else: [248:266].EntireRow.Hidden = True
End If
If rng.Value = "CT Scan Of Head Or Brain" Then
[267:285].EntireRow.Hidden = False
Else: [267:285].EntireRow.Hidden = True
End If
If rng.Value = "CT Scan Of Neck" Then
[286:304].EntireRow.Hidden = False
Else: [286:304].EntireRow.Hidden = True
End If
If rng.Value = "CT Scan Of Pelvis" Then
[305:327].EntireRow.Hidden = False
Else: [305:327].EntireRow.Hidden = True
End If
If rng.Value = "CT Scan Of Spine" Then
[328:359].EntireRow.Hidden = False
Else: [328:359].EntireRow.Hidden = True
End If
If rng.Value = "Depo Provera Injection" Then
[360:378].EntireRow.Hidden = False
[2000:2018].EntireRow.Hidden = False
Else: [360:378].EntireRow.Hidden = True
[2000:2018].EntireRow.Hidden = True
End If
If rng.Value = "Dexa Scan" Then
[379:393].EntireRow.Hidden = False
[2000:2018].EntireRow.Hidden = False
Else: [379:393].EntireRow.Hidden = True
[2000:2018].EntireRow.Hidden = True
End If
Crap: Exit Sub
End Sub

Shasur · May 11th, 2009, 08:37 PM

You can use Select Case statement like

Code:

Select Case rng.Value
    Case "Allergy Shots"
        [36:56].EntireRow.Hidden = False
    Case "Allergy Testing"
        [57:76].EntireRow.Hidden = False
 
End Select

but you need to fine tune the code for hiding the rows. Probably you can hide all rows at the start of the code and then use select like:

Code:

 
[36:2018].EntireRow.Hidden = True
Select Case rng.Value
    Case "Allergy Shots"
        [36:56].EntireRow.Hidden = False
    Case "Allergy Testing"
        [57:76].EntireRow.Hidden = False
End Select

Cheers
Shasur

Zamascus · May 12th, 2009, 09:33 AM

Thanks a ton, my macro is now seizure free (since 70 If's aren't flashing through), and requires a lot less coding since I don't have to type all the .EntireRow.Hidden = True statements