Search code examples
excelvbacombobox

Open a sheet from VBA Combobox

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?

Solution

  • 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