如何将“Sheet1"名称更改为“AAA BBB"等自定义电子表格名称? [英] How to change the 'Sheet1' name to a custom spreadsheet name like "AAA BBB"?

问题描述

我对 vba 非常陌生，这就是我如此依赖互联网搜索的原因.我现在想要做的是我想使用 ComboBox 制作一个高级过滤器，用户将在其中输入一个关键字，它会自动显示在 ListBox 上(根据用户类型提取数据).

由于我不知道这样做，我在网上搜索教程，我从

或者，使用 Worksheets("DATA STOCK") 代替 Sheet1，但这样您的代码将依赖于不重命名工作表的用户.

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?

解决方案

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.

这篇关于如何将“Sheet1"名称更改为“AAA BBB"等自定义电子表格名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！