excel - How to change the 'Sheet1' name to a custom spreadsheet name like "AAA BBB"?

Question

I'm very new to vba that's why I rely so much on internet search. What I'm trying to do now is I want to make an Advanced Filter using a ComboBox where a user will type a keyword and it will be automatically be displayed on the ListBox (Extracting data as the user type).

Since I don't don't know to do it, I searched for tutorials online and I found this code from https://www.razakmcr.in/2017/10/ms-excell-listbox-search-by-textbox-vba.html. <- that's what I'm trying to do exactly. But I noticed that his sheet is named to default 'Sheet1'. I have a sheet named "DATA STOCK" and I've tried to change Sheet1 to "DATA STOCK" but I got an error. I want to try his code because it may be a huge help.

Here is his code:

Private Sub TextBox1_Change()

Me.TextBox1.Text = StrConv(Me.TextBox1.Text, vbProperCase)
Dim i As Long
Me.ListBox1.Clear
On Error Resume Next

For i = 1 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
    a = Len(Me.TextBox1.Text)
    If Left(Sheet1.Cells(i, 1).Text, a) = Left(Me.TextBox1.Text, a) Then
        Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
        Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 2).Value
    End If
Next i
End Sub

What I did is for example: For i = 1 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A")) to For i = 1 To Application.WorksheetFunction.CountA(("DATA STOCK").Range("A:A"))

Anyway, here is my code for the WHOLE ComboBox that serves as an Advanced Filter:

Private Sub cmbSearch_Change()

    Me.cmbSearch.Text = StrConv(Me.cmbSearch.Text, vbProperCase)
    Dim i As Long
    Me.listHeader.Clear
    On Error Resume Next
    For i = 1 To x
    a = Len(Me.cmbSearch.Text)
    If Left("DATA STOCK").Cells(i, 1).Text, a) = Left(Me.cmbSearch.Text, a) Then
    Me.cmbSearch.AddItem Sheet1.Cells(i, 1).Value
    Me.cmbSearch.List(listHeader.ListCount - 1, 1) = Sheet1.Cells(i, 2).Value
    End If
    Next i

    'THE FF CODE WILL DISPLAY THE VALUE ON THE LISTBOX FROM THE COMBOBOX SELECTION
    x = Sheets("DATA STOCK").Range("A" & Rows.Count).End(xlUp).Row
        For y = 2 To x
            If Sheets("DATA STOCK").Cells(y, 1).Text = cmbSearch.Value Then
                cmbSchema.Text = Sheets("DATA STOCK").Cells(y, 1)
                cmbEnvironment.Text = Sheets("DATA STOCK").Cells(y, 2)
                cmbHost.Text = Sheets("DATA STOCK").Cells(y, 3)
                cmbIP.Text = Sheets("DATA STOCK").Cells(y, 4)
                cmbAccessible.Text = Sheets("DATA STOCK").Cells(y, 5)
                cmbLast.Text = Sheets("DATA STOCK").Cells(y, 6)
                cmbConfirmation.Text = Sheets("DATA STOCK").Cells(y, 7)
                cmbProjects.Text = Sheets("DATA STOCK").Cells(y, 8)

                UserForm1.listHeader.RowSource = "A" + CStr(y) + ": H" + CStr(y)
                Exit For
            End If
        Next y

End Sub

Can you help me how to create an Advanced Filter?

Answer 1

Sheet1 in this example is the code name of the sheet.

Your DATA STOCK sheet also has a code name, and most likely it's not DATA STOCK. Look it up in the project tree and use instead of Sheet1.

Alternatively, use Worksheets("DATA STOCK") in place of Sheet1, but then your code will rely on users not renaming the sheet.