CODEWITHSUNDEEP
excelvba
nocoup
nocoup

Reputation: 11

Combo box to switch between hidden sheets

I'm trying to make a combo-box that is able to switch between sheets in a workbook. for simplicity I want these sheets to be hidden.

I currently have this to switch between tabs but its unable to work with hidden sheets. assuming I'm going to have to unhide and hide the sheets while switching for this to work. just not sure how to go about that, as everything I have tried has not worked.

    Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub
Private Sub ComboBox1_DropButtonClick()
    Dim xSh As Worksheet
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If ComboBox1.ListCount <> ThisWorkbook.Sheets.Count Then
        ComboBox1.Clear
        For Each xSh In ThisWorkbook.Sheets
            ComboBox1.AddItem xSheet.Name
        Next xSh
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Private Sub ComboBox1_GotFocus()
    If ComboBox1.ListCount <> 0 Then ComboBox1.DropDown
End Sub

Upvotes: 1

Views: 170

Answers (1)

Toddleson
Toddleson

Reputation: 4457

You just need to interact with the Worksheet.Visible property of the sheets.

In your ComboBox1_Change event, when you're going to switch to the selected sheet, check the property and if not visible, make it visible before trying to switch to it.

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex > -1 Then
        With Sheets(ComboBox1.Text)
            If .Visible <> xlSheetVisible Then .Visible = xlSheetVisible
            .Select
        End With
    End If
End Sub

You may also want to have something to make the previous sheet re-hidden again. To do so, just set the .Visible property back to xlSheetHidden (regular) or xlSheetVeryHidden (hides it so that the user cannot make the object visible, even if they dig into the menus).

Upvotes: 1

Related Questions