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?

GSerg · Answer 1 · 2019-02-03T10:19:07.453

0

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.

edited Feb 03 '19 at 10:19

answered Feb 03 '19 at 10:15

GSerg

76,472
17
159
346

i'm sorry can you please explain it further? Something like a 14 year old without knowledge in coding would understand. I'm really having a hard time – stella Feb 03 '19 at 10:17
@stella Are you having a hard time looking up the code name or replacing occurrences of `Sheet1` from the example with `Worksheets("DATA STOCK")`? – GSerg Feb 03 '19 at 10:29
yeah actually having a hard time with creating an advanced search haha. But since I found an example I'd like to try it. And yes, I don't know how to change `Sheet1` to `Worksheets("DATA STOCK")` – stella Feb 03 '19 at 10:35
@stella You've managed to change `Sheet1.Range("A:A")` to `("DATA STOCK").Range("A:A")`, so you should be able to change it to `Worksheets("DATA STOCK").Range("A:A")` too? – GSerg Feb 03 '19 at 10:36
Example: `Sheet1.Cells` to `Worksheets("DATA STOCK").Cells` ?? Is this how it should look like? I wanna change `Sheet1` to my spreadsheet name Data Stock – stella Feb 03 '19 at 10:37

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

1 Answers1

Linked