vba to hide rows dependent on drop down list value

[rrrepp]

Hi,

I have been working on this VBA macro to hide certain rows dependent on whether the drop down list is set to 5 or 6 years (its a loan calculator). Can someone tell me what is wrong? I have put it in the sheet VBA as worksheet_change.

If Intersect(Target, Range("I6")) Is Nothing Then Exit Sub
If LCase(Range("I6").Value) = "5" Then
Range("A63:A74").EntireRow.Hidden = True
Range("A75:A75").EntireRow.Hidden = False
Range("A76:A76").EntireRow.Hidden = True
ActiveSheet.Outline.ShowLevels RowLevels:=1
ElseIf LCase(Range("I6").Value) = "6" Then
Range("A63:A74").EntireRow.Hidden = False
Range("A75:A75").EntireRow.Hidden = True
Range("A76:A76").EntireRow.Hidden = False
ActiveSheet.Outline.ShowLevels RowLevels:=1
Else
Range("A63:A76").EntireRow.Hidden = False
ActiveSheet.Outline.ShowLevels RowLevels:=1
End If


Thanks

 

[BrianExcel]

Why not do a Select Case based on the drop down value?

Sub dropdown()
dim iValue as Integer

set iValue = Sheets("SheetNameHere").listbox.value

Select Case iValue
Case "5"
Run Code for Hiding Rows here
Case "6"
Run Code for Hiding Rows here
End Select
End Sub

Would that work?

 

[rrrepp]

Sorry, but i couldn't get that to work either. It gives me object error for the 'set iValue = Sheets'.

 

[rrrepp]

I just realized i never said i was using a 'combobox'. I've tried doing something simpler.

Private Sub ComboBox1_Change()

If Range("I6").Value = "5" Then
Range("A63:A74").EntireRow.Hidden = True
Range("A75:A75").EntireRow.Hidden = False
Range("A76:A76").EntireRow.Hidden = True
ActiveSheet.Outline.ShowLevels RowLevels:=1
Else
Range("A63:A74").EntireRow.Hidden = False
Range("A75:A75").EntireRow.Hidden = True
Range("A76:A76").EntireRow.Hidden = False
ActiveSheet.Outline.ShowLevels RowLevels:=1
End If
End Sub

 

[rrrepp]

So i solved my problem. I didn't need the 'ActiveSheet.Outline.ShowLevels RowLevels:=1' so i deleted that out and it works. Here is the correct vba.


Private Sub ComboBox1_Change()

If Range("I6").Value = "5" Then
Range("A63:A74").EntireRow.Hidden = True
Range("A75:A75").EntireRow.Hidden = False
Range("A76:A76").EntireRow.Hidden = True
Else
Range("A63:A74").EntireRow.Hidden = False
Range("A75:A75").EntireRow.Hidden = True
Range("A76:A76").EntireRow.Hidden = False
End If
End Sub