我想将数据列表从用户表单传递到模块?

Question

嗨，我正在寻找有关 excel VBA 问题的帮助。我创建了一个用户表单，可让您从一个列表框到另一个列表框的项目列表中进行选择。用户窗体的代码如下：

Private Sub UserForm_Initialize()
With ColumnName
.AddItem "a"
.AddItem "b"
.AddItem "c"
.AddItem "d"
.AddItem "e"
.AddItem "f"
End With
End Sub

然后

Private Sub AddButton_Click()
For i = 0 To ColumnName.ListCount - 1
If ColumnName.Selected(i) = True Then selectedColumns.AddItem
ColumnName.List(i)
Next i
End Sub

其中，列表框1 是列名，列表框 2 是所选列。我一直在尝试通过首先输入此代码将此信息列表传递给单独的模块：

Private Sub CommandButton1_Click()
varSelectedColumns = UserForm2.selectedColumns
Unload UserForm2
End Sub

然后在模块中，我想使用这些数据来制作一个字符串，所以我有

Public varSelectedColumns As String, srtQuery As String
sub Skeleton_Query1()
UserForm2.Show
strQuery = "SEL * " & Chr(10) & varSelectedColumns
Range("B2").Value = strQuery
End Sub

Answer 1

试试...

Private Sub CommandButton1_Click()
Dim i As Long
varSelectedColumns = ""
With UserForm2.selectedColumns
If .ListCount = 0 Then
Beep
Exit Sub
End If
For i = 0 To .ListCount - 1
varSelectedColumns = varSelectedColumns & ", " & .List(i)
Next i
End With
varSelectedColumns = Mid(varSelectedColumns, 3)
Unload UserForm2
End Sub

但是，由于varSelectedColumns被分配了一个字符串，您可能希望将其名称更改为strSelectedColumns以反映其数据类型。