VBA Listbox to hide rows (2007)

[Barry.Burke]

Hello

I have a list box (Listbox2) that I would like to hide a row on a different sheet based on the selections made. I have tried a number of codes and the closest I can get is to hide all of the rows regardless of the selection.

List box references: "a","b","c"
Rows to be hidden: "a" - Sheets("Lead and Lag Indicators BU") - Cell Ref "A26"
Rows to be hidden: "b" - Sheets("Lead and Lag Indicators BU") - Cell Ref "A27"
Rows to be hidden: "c" - Sheets("Lead and Lag Indicators BU") - Cell Ref "A28"

Basically I want to be able click on "a" and hide the whole of row 26, "b" hide row 27 etc.

This is the closest I have been able to get

Sub HideRows_Click()
Dim a As Long
Sheets("Lead and Lag Indicators BU").Select
Range("A26", "A31").EntireRow.Hidden = True
Sheets("Dashboard").Select
End Sub


Private Sub ListBox2_Change()Call HideRows_Click
End Sub

Any help would be appreciated.

Thanks
Barry

 

[JoeMo]

You can do what you requested with the listbox change alone:

Private Sub ListBox2_Change()
With Sheets("Lead and Lag Indicators BU")
    .Range("A26:A28").EntireRow.Hidden = False
    Select Case Me.Index
        Case 0: .Range("A26").EntireRow.Hidden = True
        Case 1: .Range("A27").EntireRow.Hidden = True
        Case 2: .Range("A28").EntireRow.Hidden = True
    End Select
End With
End Sub

Case 0 corresponds to the value "a" and so on. The listbox indices are base 0.

 

[Barry.Burke]

You can do what you requested with the listbox change alone:

Private Sub ListBox2_Change()
With Sheets("Lead and Lag Indicators BU")
    .Range("A26:A28").EntireRow.Hidden = False
    Select Case Me.Index
        Case 0: .Range("A26").EntireRow.Hidden = True
        Case 1: .Range("A27").EntireRow.Hidden = True
        Case 2: .Range("A28").EntireRow.Hidden = True
    End Select
End With
End Sub

Case 0 corresponds to the value "a" and so on. The listbox indices are base 0.

Hi Joe

That was exactly what I needed

Thanks

Barry