VBA多重列表框,填充选定项目列 [英] VBA multiselect listbox, populate column with selected items
问题描述
我有一个多重列表框,其中包含了另一张表格中独特的城市名称。列表中只有一列数据。它包含下面的代码,这是在堆栈交换中的一些可爱的人的帮助下开发的。
I have a multiselect listbox populated with unique city names from another sheet. It only has one column of data in the list. It is populated with the code below which was developed with some assistance from some lovely folks on stack exchange.
从此列表框中,我想使用用户选择的项目在同一张表中填充列a。我很确定这只是几行代码,但我不知道该怎么做,我没有成功地计算列表中选择的项目。
From this listbox I would like to take the items selected by the user to populate column a in the same sheet. I'm pretty sure this is only a couple of lines of code but I am not sure how to go about it, I have not had any success in counting the items selected in the list.
任何帮助不胜感激。
干杯
Sub FilterUniqueData_multi()
Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp() As Variant
ReDim temp(0)
Dim Value1 As Variant
Dim endrow As Long
On Error Resume Next
Set Billed_sheet = Workbooks("Billed_customers.xlsx").Sheets("Non Household Metered Users")
With Billed_sheet
'clear formatting to get rid of merging
.Range("a:v").ClearFormats
endrow = .Range("a" & .Rows.count).End(xlUp).Row
.Range("A2:v" & endrow).Sort _
Key1:=.Range("h2"), Order1:=xlAscending 'essential to qualify the range on both lines with '.'
temp = .Range("h2:h" & endrow).Value
End With
For Each Value In temp
If Len(Value) > 0 Then test.Add Value, CStr(Value)
Next Value
ReDim temp(0)
Workbooks("DMA_metered_tool_v4.xlsm").Worksheets("DMA list").Shapes("DMA_listbox").ControlFormat.RemoveAllItems
For Each Value In test
Worksheets("DMA list").Shapes("DMA_listbox").ControlFormat.AddItem Value
Next Value
Set test = Nothing
Worksheets("DMA list").Shapes("DMA_listbox").ControlFormat.MultiSelect = xlSimple
End Sub
推荐答案
您需要迭代列表中的项目, 。选择
属性,然后输出该列表值:
You need to iterate the items in the list, check the .Selected
property and then output that list value if appropriate:
Sub Outputdata()
Dim wsList As Worksheet
Dim lb As ListBox
Dim n As Long
Set wsList = Workbooks("DMA_metered_tool_v4.xlsm").Worksheets("DMA list")
Set lb = wsList.ListBoxes("DMA_listbox")
For n = 1 To lb.ListCount
If lb.Selected(n) Then
wsList.Cells(wsList.Rows.Count, "A").End(xlUp).Offset(1).Value = lb.List(n)
End If
Next n
End Sub
这篇关于VBA多重列表框,填充选定项目列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!