CODEWITHSUNDEEP
vbaexcel
TisButaScratch
TisButaScratch

Reputation: 173

How to add sheet names containing a specific string to a user form combo box

I am attempting to write code that cycles through all the worksheets in my current excel, and if the worksheet name contains the word "Pack" it is added to my userform combobox. My code is not producing an error, but the combo box appears to be empty.

Private Sub UserForm_Initialize()

Dim wS As Worksheet

PackComboBox.Font.Size = 12

    For Each wS In Sheets
        If LCase(wS.Name) Like "*Pack*" Then
            With PackComboBox
            .AddItem wS
            End With
        Else
        End If
        Next wS

    End Sub

Any help would be appreciated, thanks!

Upvotes: 0

Views: 133

Answers (1)

user9298223
user9298223

Reputation:

You have a capital letter in you LCase comparison:

If LCase(wS.Name) Like "Pack" Then

If LCase(wS.Name) Like "pack" Then

It is more efficient to have the With clause surround the For Each loop. In this way the PackComboBox only needs to be resolved 1 time.

With PackComboBox
    For Each wS In Sheets
        If LCase(wS.Name) Like "*pack*" Then
            .AddItem wS.Name
        Else
        
        End If
    Next wS
End With

Upvotes: 2

Related Questions