vba 根据列表框选择（启用多选）从 Excel 工作表填充组合框

populate combobox from an excel sheet based on listbox selection (multiselect enabled)

提问by user3159175

I have a listbox with 3 values and multi select option enabled. Based on the values selected, I want to populate a combobox by referring to certain ranges in excel sheet. The code below works if only one value is selected from listbox. I know it will not work for multiple selections, but I am unable to write code to loop through the list of selections and then add corresponding ranges to combo box.

我有一个包含 3 个值和多选选项的列表框。根据选择的值，我想通过引用 Excel 表中的某些范围来填充组合框。如果仅从列表框中选择一个值，则下面的代码有效。我知道它不适用于多个选择，但我无法编写代码来循环选择列表，然后将相应的范围添加到组合框。

So, if I select customer1 and customer2, i want the values in ranges d3:d34 AND c4:c24 populated in the combobox. similarly for other combinations too. However, if one of the values is unselected, corresponding values should be cleared from the combobox.

因此，如果我选择 customer1 和 customer2，我希望在组合框中填充 d3:d34 和 c4:c24 范围内的值。其他组合也类似。但是，如果未选择其中一个值，则应从组合框中清除相应的值。

If I were to save all the selected items in listbox in an array, how do i do a "search" or "find" for each of the values stored in the array?

如果我要将列表框中的所有选定项目保存在一个数组中，我如何对存储在数组中的每个值进行“搜索”或“查找”？

Please suggest how I could revise this code.

请建议我如何修改此代码。

Thank you!

谢谢！

Private Sub Combobox1_DropButtonClick()

    Dim curSelected As String
    Dim i As Integer

    For i = 0 To Listbox1.ListCount - 1
        If Listbox1.Selected(i) Then
            curSelected = Listbox1.List(i)
                If curSelected = "customer1" Then
                    ChooseDeals.List = Sheets("Sheet3").Range("d4:d34").Value
                ElseIf curSelected = "customer2" Then
                    ChooseDeals.List = Sheets("Sheet3").Range("c4:c24").Value
                ElseIf curSelected = "customer3" Then
                    ChooseDeals.List = Sheets("Sheet3").Range("e4:e20").Value
                End If
          End If
    Next i


End Sub

回答by Doug Glancy

I don't know of any way to set a ComboBox.Listto a discontiguous Range, so I think you're forced to add the values one cell at a time.

我不知道有什么方法可以将 a 设置ComboBox.List为不连续的Range，所以我认为您不得不一次添加一个单元格的值。

This code takes advantage of the Range.Areasproperty, and the fact that the Areas are defined discretely in the order they appear in the assignment.

这段代码利用了Range.Areas属性，以及 Areas 按照它们在赋值中出现的顺序离散定义的事实。

For example if you type this in the Debug window:

例如，如果您在“调试”窗口中键入：

? Activesheet.Range("B1,A1").Areas.Count 

you get 2, even though it's a contiguous range that would yield only one Areaif you defined it as:

你得到 2，即使它是一个连续的范围，Area如果你将它定义为：

? Activesheet.Range("A1:B1").Areas.Count

Further, if you alter the first one to:

此外，如果您将第一个更改为：

? Activesheet.Range("B1,A1").Areas(1).Address

you'll get "B1" even though "A1" appears before it in the spreadsheet.

即使电子表格中“A1”出现在“B1”之前，您也会得到“B1”。

So, I thought you could use this to "line up" your ListBoxitems and their selected Ranges:

所以，我想你可以用它来“排列”你的ListBox项目和他们选择的Ranges：

Private Sub ComboBox1_DropButtonClick()
Dim ws As Excel.Worksheet
Dim FullRange As Excel.Range
Dim RangeToUse As Excel.Range
Dim i As Integer
Dim cell As Excel.Range
Dim AreaToUse

Set ws = ThisWorkbook.Worksheets("Sheet3")
Set FullRange = ws.Range("d4:d34,c4:c24,e4:e20")
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        If RangeToUse Is Nothing Then
            Set RangeToUse = FullRange.Areas(i + 1)
        Else
            Set RangeToUse = Union(RangeToUse, FullRange.Areas(i + 1))
        End If
    End If
Next i
If Not RangeToUse Is Nothing Then
    For Each AreaToUse In RangeToUse
        For Each cell In AreaToUse
            ComboBox1.AddItem cell.Value
        Next cell
    Next AreaToUse
End If
End Sub

回答by Peekay

I did a similar thing recently. I called a validation sub on selecting the cell in which the combobox is placed. The validation program would dynamically check how many values are there in a list that is maintained separately and populate the data validation list for the cell accordingly. A separate sub will list may have to be created which updates the list. and this sub may be called before populating the data validation list. The following code worked for me. You may have to use a variant.

我最近做了类似的事情。我在选择放置组合框的单元格时调用了验证子。验证程序将动态检查单独维护的列表中有多少值，并相应地填充单元格的数据验证列表。可能必须创建一个单独的子遗嘱列表来更新列表。并且可以在填充数据验证列表之前调用此子程序。以下代码对我有用。您可能必须使用变体。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("D7")) Is Nothing Then

    Call getValidation

End If
End Sub

Sub getValidation()

Dim j As Integer
j = 0

   j = Range("Charts!XFA1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row

' data list stored in Charts!XFA column. Found last row where the list ends.

If j > 1 Then

Range("QueryingTool!D7").Select  'D7 is the cell where data validation is used
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Charts!$XFA7:$XFA" & j
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
End If

End Sub