How to add a list of specific worksheets to the list in ComboBox VBA

1
votes

I found a way to add all of the worksheets in the workbook into the list of the ComboBox on the UserForm, which is done with use of the following code

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    For Each ws In Worksheets
        cmbSheet.AddItem ws.Name
    Next ws
End Sub

My problem is that I only need to add some specific worksheets and not all of them. For example my user will select specific sheet and by clicking "Continue" button should end up on the selected worksheet to continue his/her task. My workbook holds several worksheets, some of which are used to output data (Reports) and some worksheets contain so called templates, which I want my user (only those) to be able to select from the ComboBox I have talked above.

Can you guys help me please?

2
vbaexcel

2 Answers

2
votes

perhaps you could use a naming convention for the worksheets?

if the reports you want the user to select all have the word temmplate in their name you could do something like this:

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    For Each ws In Worksheets
        if instr(lcase(ws.name),"template")<>0 then
            cmbSheet.AddItem ws.Name
        end if
    Next ws
End Sub
3
votes
Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    For Each ws In Worksheets
        if ws.Name like "*Template" then cmbSheet.AddItem ws.Name
    Next ws
End Sub