CODEWITHSUNDEEP
excelvbacombobox
Michi
Michi

Reputation: 5471

Only show visible sheets in ComboBox

I have created the following Excel-spreadsheet:

      A      B         C         D         E
1   Sheet1
2   Sheet2
3   Sheet5
4   Sheet6
5   Sheet8
6   Sheet9
7

As you can see, in Column A I have listed some of the sheets within the Excel file.

Now, I use the below VBA to get this list into a ComboBox:

Sub UserForm_Activate()
  ComboBox1.List = Sheet1.Range("A1:A8").Value
End Sub

All this works fine so far.

However, now it can happen that some of the sheets are not visible. Therefore, I also do not want them to appear in the ComboBox.

One solution I found was the answer from here. However, this VBA only works over the entire Excel file but as you can see in my list above some sheets (Sheet3, Sheet4, Sheet7) are excluded from the list. Those sheets should also remain excluded no matter if they are visible or not.

So for example, when I make Sheet2 and Sheet6 invisible the list in the ComboBox should automatically be adjusted and look like this:

Sheet1
Sheet5
Sheet8
Sheet9

What do I need to change in my VBA to achieve this?

Upvotes: 0

Views: 475

Answers (2)

Xabier
Xabier

Reputation: 7735

The code below puts the sheets from the worksheet into a dynamic array and checks if it's visible, so you're not hard-coding the visible sheets. Therefore, excluding the sheets you don't want (Sheet3, Sheet4 and Sheet7) from the worksheet will also exclude them from your ComboBox.

 Private Sub UserForm_Activate()
    Dim sheet_list As Variant
    sheet_list = Sheet1.Range("A1:A8").Value
    'get the list of Sheets from Column A
    Dim combo_list As Variant
    combo_list = Array()
    'create and empty array

    Dim sheet_name As Variant
    For Each sheet_name In sheet_list
    'loop through sheets
        If ThisWorkbook.Worksheets(sheet_name).Visible Then
        'check if they are visible
            Dim ub As Long
            ub = UBound(combo_list) + 1
            ReDim Preserve combo_list(ub)
            'increment array dimension
            combo_list(ub) = sheet_name
            'add Sheet to array
        End If
    Next sheet_name

    ComboBox1.List = combo_list
    'populate the ComboBox
End Sub

Upvotes: 1

Teamothy
Teamothy

Reputation: 2016

Based on linked topic I modified the code from answer to reach what You want to achieve

Private Sub ChangeComboBox()
    Dim xSheet As Worksheet

    ' Clear the combobox
    ComboBox1.Clear

    ' Add the sheets again
    For Each xSheet In ThisWorkbook.Sheets
        '~~> Check if they are visible
        If xSheet.Visible = xlSheetVisible Then
            If xSheet.Name <> Sheet3.Name And xSheet.Name <> Sheet4.Name And xSheet.Name <> Sheet7.Name Then
                ComboBox1.AddItem xSheet.Name
            End If
        End If
    Next
End Sub

Upvotes: 1

Related Questions