If ElseIf

[ajm]

hi all,

Need some help fixing a macro. am trying to hide all rows and only unhide the relevant ones based on what users select from a drop down box.
This is what i have so far but am not on the right track. any one care to help me fix it up? In case you are wondering why my syntax looks really odd, i tried adapting a select case macro to work for the list.

Sub Hide_other_operators(ByVal Target As Range)
Dim Sht As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("f172")) Is Nothing Then Exit Sub
Set Sht = Sheets("Parameters and Assumptions")
With Sht
.Rows("187:2786").RowHeight = 0
ElseIf Target = "QRail" Then
.Rows("187:339").AutoFit
ElseIf Target = "Bribie" Then
.Rows("340:492").AutoFit
ElseIf Target = "BrisbaneTransport" Then
.Rows("493:645").AutoFit
ElseIf Target = "BCCFerries" Then
.Rows("646:798").AutoFit
ElseIf Target = "Buslink" Then
.Rows("799:951").AutoFit
ElseIf Target = "Caboolture" Then
.Rows("952:1104").AutoFit
ElseIf Target = "Clarks" Then
.Rows("1105:1257").AutoFit
ElseIf Target = "Hornibrook" Then
.Rows("1258:1410").AutoFit
ElseIf Target = "Kangaroo" Then
.Rows("1411:1563").AutoFit
ElseIf Target = "MtGravatt" Then
.Rows("1564:1716").AutoFit
ElseIf Target = "National" Then
.Rows("1717:1869").AutoFit
ElseIf Target = "ParkRidge" Then
.Rows("1870:2022").AutoFit
ElseIf Target = "Sunbus" Then
.Rows("2023:2175").AutoFit
ElseIf Target = "Thompson" Then
.Rows("2176:2328").AutoFit
ElseIf Target = "Westside" Then
.Rows("2329:2481").AutoFit
ElseIf Target = "SouthernCross" Then
.Rows("2482:2634").AutoFit
ElseIf Target = "Surfside" Then
.Rows("2635:2787").AutoFit
ElseIf Target = "AllOPerators" Then
.Rows("187:2940").AutoFit
End If
End Sub

 

[jindon]

Hi
Is loolks line Worksheet_Change event code, so
change

Sub Hide_other_operators(ByVal Target As Range)

To

Private Sub Worksheet_Change(ByVal Target As Range)

And paste the code onto sheet module.

 

[ajm]

can't get that to work either. anything else come to mind?

 

[jindon]

ajm

Did you paste the new code onto sheet module?
If so, can you post your entire sheet module code(s)?

 

[jindon]

ajm

Stick to your original thread!!

 

[ajm]

jindon. cleaned up some fluff that I had ben mucking around with and it now works fine. many thanks for your help.

 

[ajm]

jindon, i have one lingering problem. this is the entire macro:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Sht As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("f172")) Is Nothing Then Exit Sub
Set Sht = Sheets("Parameters and Assumptions")
With Sht
.Rows("187:2786").RowHeight = 0
If Target = "QRail" Then
.Rows("187:337").AutoFit
ElseIf Target = "Bribie" Then
.Rows("340:492").AutoFit
ElseIf Target = "BrisbaneTransport" Then
.Rows("493:645").AutoFit
ElseIf Target = "BCCFerries" Then
.Rows("646:798").AutoFit
ElseIf Target = "Buslink" Then
.Rows("799:951").AutoFit
ElseIf Target = "Caboolture" Then
.Rows("952:1104").AutoFit
ElseIf Target = "Clarks" Then
.Rows("1105:1257").AutoFit
ElseIf Target = "Hornibrook" Then
.Rows("1258:1410").AutoFit
ElseIf Target = "Kangaroo" Then
.Rows("1411:1563").AutoFit
ElseIf Target = "MtGravatt" Then
.Rows("1564:1716").AutoFit
ElseIf Target = "National" Then
.Rows("1717:1869").AutoFit
ElseIf Target = "ParkRidge" Then
.Rows("1870:2022").AutoFit
ElseIf Target = "Sunbus" Then
.Rows("2023:2175").AutoFit
ElseIf Target = "Thompson" Then
.Rows("2176:2328").AutoFit
ElseIf Target = "Westside" Then
.Rows("2329:2481").AutoFit
ElseIf Target = "SouthernCross" Then
.Rows("2482:2634").AutoFit
ElseIf Target = "Surfside" Then
.Rows("2635:2787").AutoFit
ElseIf Target = "AllOPerators" Then
.Rows("187:2940").AutoFit
End If
End With
End Sub

The problem occurs when the user selects the first option in the drop down box: QRail. In the macro, the only way i could get it to run was to put this in:

If Target = "QRail" Then
.Rows("187:337").AutoFit

as the macro required an initial "If". where else can I put this IF so that if the user selects QRAIL, only the QRail section is shown and the rest hidden?