问题描述
带有 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!