时间戳

首页 > 解决方案 > 使用列表框多选从列表中返回多个索引时出错

excel - 使用列表框多选从列表中返回多个索引时出错

问题描述

VBA 新手，有些沮丧。试图为我的老师们制作一个表格来简化文书工作。

我有一个表格 1. 苹果
2. 橙子
3. 葡萄

如果用户要从列表中选择苹果和葡萄，我希望单元格只有索引。所以要在单元格中打印 (1,3)。我不想要这些话。

我当前的代码

Private Sub SpedAccomAddBtn_Click()

'variable to count multiple selections'
VarSped = " "

'loop to keep track of indexes of selected items'
For X = 0 To Me.SpedListBx.ListCount - 1 'count through list
    If Me.SpedListBx.Selected(X) Then    
        If VarSped = " " Then  'if blank then record first item'
            VarSped = Me.SpedListBx.ListIndex + 1 'first selected item. +1 because excel is a 0 based index'
        Else 'if not the first selection add a , between selections'
            VarSped = VarSped & "," & Me.SpedListBx.ListIndex + 1
        End If
    End If
Next X

ThisWorkbook.Sheets("Master SPED Sheet").Range("c4") = VarSped 'print to cell'

如果我使用前面选择 Apple 和 Grape 的示例，我得到 (3,3) 而不是 (1,3)。我不明白为什么 VarSped 不断被覆盖。（我是编码新手，我必须评论所有内容，所以我觉得我知道自己在做什么）

标签： excelvbalistbox

解决方案

试试这个，看看如何在循环中引用当前项目：

Private Sub SpedAccomAddBtn_Click()

Dim VarSped As String
Dim x As Integer

'variable to count multiple selections'
VarSped = " "

'loop to keep track of indexes of selected items'
For x = 0 To Me.SpedListBx.ListCount - 1 'count through list
    If Me.SpedListBx.Selected(x) Then
        If VarSped = " " Then  'if blank then record first item'
            VarSped = Me.SpedListBx.List(x) 'first selected item. +1 because excel is a 0 based index'
        Else 'if not the first selection add a , between selections'
            VarSped = VarSped & "," & Me.SpedListBx.List(x)
        End If
    End If
Next x

ThisWorkbook.Sheets("Master SPED Sheet").Range("c4") = VarSped 'print to cell'

End Sub

推荐阅读