我想使用 ListBox 以便我可以选择条目而不是 TextBox [英] I wanted to use ListBox so that I can select the entry rather than TextBox

问题描述

带有 vba 的 Excel 2013:

Excel 2013 with vba:

我在 Sheet1 中有 2 列，A 列有 NBA 球员，B 列显示他们的球衣号码.如果我在 txtNumber 中输入 2，它将在球衣上显示编号为 2 的球员.它以这种方式工作，但是我无法单击或选择条目或数据.我认为 Listbox 将是 TextBox 的更好替代品，但我只是不知道如何使用列表框.请帮忙.

I have 2 columns in Sheet1 Column A has NBA Players while Column B shows their Jersey Numbers. If I type 2 in txtNumber it will display Players with number 2 on their Jerseys. It works on that way, However I can't click or select the entry or data. I'm thinking that Listbox would be a better replacement for TextBox, however I just don't know how to use the listbox. Please help.

截图

code:

Private Sub txtNumber_Change()

Dim mySheet As Worksheet    'declaring mySheet as the Worksheet...
Dim x
Dim i As Long
Dim str As String
Set mySheet = Sheets("Sheet1")

x = mySheet.Range("A1").CurrentRegion.Value

For i = 2 To UBound(x, 1)
    If x(i, 2) = Val(txtNumber.Value) Then
        If str = "" Then
            str = x(i, 1)
        Else
            str = str & vbNewLine & x(i, 1)
        End If
    End If
Next i

If str <> "" Then
    txtName.Value = str
Else
    txtName.Value = "Match not found"
End If

End Sub

推荐答案

假设列表框的名称是 ListBox1 那么你可以尝试这样的事情...

Assuming the name of the listbox is ListBox1 then you may try something like this...

Private Sub txtNumber_Change()
Dim mySheet As Worksheet    'declaring mySheet as the Worksheet...
Dim x, dict
Dim i As Long
Dim cnt As Long
Set mySheet = Sheets("Sheet1")
ListBox1.Clear
x = mySheet.Range("A1").CurrentRegion.Value
Set dict = CreateObject("Scripting.Dictionary")
If Application.CountIf(mySheet.Columns(2), txtNumber.Value) > 0 Then
    For i = 2 To UBound(x, 1)
        If x(i, 2) = Val(txtNumber.Value) Then
            dict.Item(x(i, 1)) = ""
        End If
    Next i
    ListBox1.List = dict.keys
Else
    ListBox1.AddItem "Match not found"
End If
End Sub

这篇关于我想使用 ListBox 以便我可以选择条目而不是 TextBox的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！