Open a sheet from VBA Combobox-CodePudding

I have 199 supplier names in my datasheet. Every one of them has a spreadsheet for themselves. As you can guess I need to find the necessary spreadsheet manually by clicking. So how could I automate the process with VBA via Combobox?

CodePudding user response：

I'll get you started. Create a userform. In my example, I've left it named as the default 'Userform1'

On it, I've placed a ComboBox. Again, default name is ComboBox1.

I then added the following code to the userform that populates the box on displaying the userform:

Private Sub UserForm_Initialize()
    Refresh_ComboBox
End Sub

Private Sub Refresh_ComboBox()
    ComboBox1.Clear
    For Each sh In ThisWorkbook.Sheets
        If sh.Visible = xlSheetVisible Then ComboBox1.AddItem sh.Name
    Next
End Sub

Then I added the following event that activates the selected worksheet when the ComboBox value is changed:

Private Sub ComboBox1_Change()
    ThisWorkbook.Sheets(ComboBox1.Value).Activate
End Sub

Things you may want to consider to improve the functionality:

Should the userform disappear once used?
Should it be non-modal?
What if a new worksheet is added?
This only deals with visible sheets.. What about hidden?

CodePudding user response：

Thanks, everyone.

I figure it out with Listbox.

Private Sub TextBox17_Change()

Dim i As Long
Me.TextBox17.Text = StrConv(Me.TextBox17.Text, 1)
Me.ListBox4.Clear
For i = 2 To Application.WorksheetFunction.CountA(Sayfa1.Range("A:A"))
a = Len(Me.TextBox17.Text)
'If Left(Sayfa2.Cells(i, 3).Value, a) = Left(Me.TextBox17.Text, a) Then
If Sayfa1.Cells(i, 1).Value Like "*" & TextBox17.Text & "*" Then
Me.ListBox4.AddItem Sayfa1.Cells(i, 1).Value
Me.ListBox4.List(ListBox4.ListCount - 1, 3) = Sayfa1.Cells(i, 1).Value
End If
Next i

End Sub

Private Sub ListBox4_Click()

    Dim x As Variant
        x = ListBox4.Text
        Sheets(x).Select
    Unload Me

End Sub