VBA Dropdown to Navigate between Hidden Sheet using Combo Box ActiveX Control

[ghojezz]

Good Morning,

I'm sorry if similar question had been asked for several times before, I would like to make dropdown list to navigate between hidden sheet using combo box ActiveX. The script for dropdown sheet list it self I already have it from here like this:

Private Sub ComboBox1_Change()    If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub


Private Sub ComboBox1_DropButt*******()
    Dim xSheet As Worksheet
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If ComboBox1.ListCount <> ThisWorkbook.Sheets.Count Then
        ComboBox1.Clear
        For Each xSheet In ThisWorkbook.Sheets
            ComboBox1.AddItem xSheet.Name
        Next xSheet
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub


Private Sub ComboBox1_GotFocus()
    If ComboBox1.ListCount <> 0 Then ComboBox1.DropDown
End Sub

The code is about an inch close to what I really need which is show only active sheet. So the flow is like this:

1. All sheets are hidden except "Homepage" sheet
2. Select sheet "Financial Asset" from dropdown list -> Unhide and jump to "Financial Asset" sheet, and hide the "Homepage" sheet
3. There's a box command button for "Return to Homepage" feature with the same function like no 2 which is hide the "Financial Asset" sheet and unhide "Homepage" sheet

Thank you

 

[lhartono]

Try This...

[COLOR=#d3d3d3]'[/COLOR]
[COLOR=#0000cd]Private Sub ComboBox1_Change()
Const shtFina = "Financial Asset"
Const shtHome = "Homepage"

Application.ScreenUpdating = False
If ComboBox1.ListIndex > -1 Then
    Select Case Me.ComboBox1.Text
        Case shtFina
            With Sheets(ComboBox1.Text)
            .Visible = True
            .Activate
            End With
            If Sheets(shtHome).Visible Then Sheets(shtHome).Visible = False
        Case shtHome
            With Sheets(ComboBox1.Text)
            .Visible = True
            .Activate
            End With
            If Sheets(shtFina).Visible Then Sheets(shtFina).Visible = False
    End Select
End If
Application.ScreenUpdating = True
End Sub[/COLOR]