vba - 如何在文本框中显示列表框结果?
问题描述
我有一个从表 3 中的表格中提取信息的列表框。然后它允许我在列表框中选择一个项目,然后将行的其余部分显示在列表框网格上方的文本框中。
下面的代码可以工作,但如果表中有很多项目,就会出现问题。在创建不需要多次复制和粘贴代码的循环程序时,我需要一些帮助。
我尝试的方法是将列表框用作数组。
Private Sub ListBoxSearchResults_Click()
Worksheets("Stock Data").Activate
'Verify that an item was selected
If ListBoxSearchResults.ListIndex = -1 Then
'If ListIndex is -1, nothing selected
MsgBox "Nothing was selected!"
Else
If frmProductSearch.ListBoxSearchResults.Selected(0) = True Then
TextBoxDate.Value = Worksheets("Product Search").Range("a2")
TextBoxDistance.Value = Worksheets("Product Search").Range("b2")
TextBoxType.Value = Worksheets("Product Search").Range("c2")
TextBoxElevation.Value = Worksheets("Product Search").Range("D2")
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e2")
TextBoxPower.Value = Worksheets("Product Search").Range("f2")
TextBoxCalories.Value = Worksheets("Product Search").Range("g2")
TextBoxPowerOther = Worksheets("Product Search").Range("h2")
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i2")
Else
If frmProductSearch.ListBoxSearchResults.Selected(1) = True Then
TextBoxDate.Value = Worksheets("Product Search").Range("a3")
TextBoxDistance.Value = Worksheets("Product Search").Range("b3")
TextBoxType.Value = Worksheets("Product Search").Range("c3")
TextBoxElevation.Value = Worksheets("Product Search").Range("D3")
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e3")
TextBoxPower.Value = Worksheets("Product Search").Range("f3")
TextBoxCalories.Value = Worksheets("Product Search").Range("g3")
TextBoxPowerOther = Worksheets("Product Search").Range("h3")
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i3")
Else
If frmProductSearch.ListBoxSearchResults.Selected(2) = True Then
TextBoxDate.Value = Worksheets("Product Search").Range("a4")
TextBoxDistance.Value = Worksheets("Product Search").Range("b4")
TextBoxType.Value = Worksheets("Product Search").Range("c4")
TextBoxElevation.Value = Worksheets("Product Search").Range("D4")
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e4")
TextBoxPower.Value = Worksheets("Product Search").Range("f4")
TextBoxCalories.Value = Worksheets("Product Search").Range("g4")
TextBoxPowerOther = Worksheets("Product Search").Range("h4")
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i4")
Else
If frmProductSearch.ListBoxSearchResults.Selected(3) = True Then
TextBoxDate.Value = Worksheets("Product Search").Range("a5")
TextBoxDistance.Value = Worksheets("Product Search").Range("b5")
TextBoxType.Value = Worksheets("Product Search").Range("c5")
TextBoxElevation.Value = Worksheets("Product Search").Range("D5")
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e5")
TextBoxPower.Value = Worksheets("Product Search").Range("f5")
TextBoxCalories.Value = Worksheets("Product Search").Range("g5")
TextBoxPowerOther = Worksheets("Product Search").Range("h5")
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i5")
Else
If frmProductSearch.ListBoxSearchResults.Selected(4) = True Then
TextBoxDate.Value = Worksheets("Product Search").Range("a6")
TextBoxDistance.Value = Worksheets("Product Search").Range("b6")
TextBoxType.Value = Worksheets("Product Search").Range("c6")
TextBoxElevation.Value = Worksheets("Product Search").Range("D6")
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e6")
TextBoxPower.Value = Worksheets("Product Search").Range("f6")
TextBoxCalories.Value = Worksheets("Product Search").Range("g6")
TextBoxPowerOther = Worksheets("Product Search").Range("h6")
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i6")
Else
If frmProductSearch.ListBoxSearchResults.Selected(5) = True Then
TextBoxDate.Value = Worksheets("Product Search").Range("a7")
TextBoxDistance.Value = Worksheets("Product Search").Range("b7")
TextBoxType.Value = Worksheets("Product Search").Range("c7")
TextBoxElevation.Value = Worksheets("Product Search").Range("D7")
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e7")
TextBoxPower.Value = Worksheets("Product Search").Range("f7")
TextBoxCalories.Value = Worksheets("Product Search").Range("g7")
TextBoxPowerOther = Worksheets("Product Search").Range("h7")
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i7")
Else
If frmProductSearch.ListBoxSearchResults.Selected(6) = True Then
TextBoxDate.Value = Worksheets("Product Search").Range("a8")
TextBoxDistance.Value = Worksheets("Product Search").Range("b8")
TextBoxType.Value = Worksheets("Product Search").Range("c8")
TextBoxElevation.Value = Worksheets("Product Search").Range("D8")
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e8")
TextBoxPower.Value = Worksheets("Product Search").Range("f8")
TextBoxCalories.Value = Worksheets("Product Search").Range("g8")
TextBoxPowerOther = Worksheets("Product Search").Range("h8")
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i8")
Else
If frmProductSearch.ListBoxSearchResults.Selected(7) = True Then
TextBoxDate.Value = Worksheets("Product Search").Range("a9")
TextBoxDistance.Value = Worksheets("Product Search").Range("b9")
TextBoxType.Value = Worksheets("Product Search").Range("c9")
TextBoxElevation.Value = Worksheets("Product Search").Range("D9")
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e9")
TextBoxPower.Value = Worksheets("Product Search").Range("f9")
TextBoxCalories.Value = Worksheets("Product Search").Range("g9")
TextBoxPowerOther = Worksheets("Product Search").Range("h9")
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i9")
Else
If frmProductSearch.ListBoxSearchResults.Selected(8) = True Then
TextBoxDate.Value = Worksheets("Product Search").Range("a10")
TextBoxDistance.Value = Worksheets("Product Search").Range("b10")
TextBoxType.Value = Worksheets("Product Search").Range("c10")
TextBoxElevation.Value = Worksheets("Product Search").Range("D10")
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e10")
TextBoxPower.Value = Worksheets("Product Search").Range("f10")
TextBoxCalories.Value = Worksheets("Product Search").Range("g10")
TextBoxPowerOther = Worksheets("Product Search").Range("h10")
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i10")
Else
If frmProductSearch.ListBoxSearchResults.Selected(9) = True Then
TextBoxDate.Value = Worksheets("Product Search").Range("a11")
TextBoxDistance.Value = Worksheets("Product Search").Range("b11")
TextBoxType.Value = Worksheets("Product Search").Range("c11")
TextBoxElevation.Value = Worksheets("Product Search").Range("D11")
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e11")
TextBoxPower.Value = Worksheets("Product Search").Range("f11")
TextBoxCalories.Value = Worksheets("Product Search").Range("g11")
TextBoxPowerOther = Worksheets("Product Search").Range("h11")
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i11")
解决方案
你需要遍历所有的.Selected
吗?似乎更容易使用 ListIndex 就像在第一次检查中一样。然后我们根本不需要循环,只需选择索引,并将其偏移 2 即可获得行。
Private Sub ListBoxSearchResults_Click()
Dim lIndex As Long
lIndex = frmProductSearch.ListBoxSearchResults.listIndex
Worksheets("Stock Data").Activate 'Verify that an item was selected
If lIndex = -1 Then 'If ListIndex is -1, nothing selected
MsgBox "Nothing was selected!"
Else
TextBoxDate.Value = Worksheets("Product Search").Range("a" & lIndex + 2)
TextBoxDistance.Value = Worksheets("Product Search").Range("b" & lIndex + 2)
TextBoxType.Value = Worksheets("Product Search").Range("c" & lIndex + 2)
TextBoxElevation.Value = Worksheets("Product Search").Range("D" & lIndex + 2)
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e" & lIndex + 2)
TextBoxPower.Value = Worksheets("Product Search").Range("f" & lIndex + 2)
TextBoxCalories.Value = Worksheets("Product Search").Range("g" & lIndex + 2)
TextBoxPowerOther = Worksheets("Product Search").Range("h" & lIndex + 2)
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i" & lIndex + 2)
End If
End Sub
如果你必须遍历它们,你应该能够使用做类似的事情:
Private Sub ListBoxSearchResults_Click()
Dim i As Long
Worksheets("Stock Data").Activate 'Verify that an item was selected
If frmProductSearch.ListBoxSearchResults.listIndex = -1 Then 'If ListIndex is -1, nothing selected
MsgBox "Nothing was selected!"
Else
For i = 0 To frmProductSearch.ListBoxSearchResults.ListCount
If frmProductSearch.ListBoxSearchResults.Selected(i) = True Then
TextBoxDate.Value = Worksheets("Product Search").Range("a" & i + 2)
TextBoxDistance.Value = Worksheets("Product Search").Range("b" & i + 2)
TextBoxType.Value = Worksheets("Product Search").Range("c" & i + 2)
TextBoxElevation.Value = Worksheets("Product Search").Range("D" & i + 2)
TextBoxHeartRate.Value = Worksheets("Product Search").Range("e" & i + 2)
TextBoxPower.Value = Worksheets("Product Search").Range("f" & i + 2)
TextBoxCalories.Value = Worksheets("Product Search").Range("g" & i + 2)
TextBoxPowerOther = Worksheets("Product Search").Range("h" & i + 2)
TextBoxCaloriesSpeed = Worksheets("Product Search").Range("i" & i + 2)
End If
Next i
End If
End Sub
推荐阅读
- c# - 迭代字典内的字典
- python - 如何在 Python 中漂亮地打印经过时间/ETA?
- python - 使用 sagemaker 的 AWS Kinesis 实时视频处理
- javascript - filter(cond) 和 flatMap(x => cond? of(x) : EMPTY) 之间有区别吗?
- java - Android:LinearLayout.addView 不呈现添加的视图
- shell - 用于合并具有重复键的 dotenv 文件的 Shell 脚本
- android - 在 Webview 内的内容之间添加视图
- sql - 如何在postgresql中将位变化转换为数字
- python-3.x - 计算时间序列中每个点的斜率
- python - 从 .py 文件调用 python 模块