Excel VBA - 将值插入到多列组合框中 [英] Excel VBA - inserting values into multicolumn combobox

问题描述

我在Excel的用户表单上有两个组合框。当我选择第一个组合框时，它会过滤当前工作表上的行，并显示combobox2中的值。

我希望combobox2是一个多列组合框，复制并粘贴多个值从一个工作表到另一个，但我不能得到多个值到combobox2。这是我一起黑客攻击的代码。我可以过滤的结果，当我改变combobox1中的值，但combobox2显示一个单一的值：

  Private Sub ComboBox1_Change ）
 Me.ComboBox2.Clear 
 
当用户在组合框中选择不同的选项时，过滤组合框2中的结果1 
 Dim wb As Workbook 
 Dim ws As Worksheet 
 Dim copy从范围，aCell As范围
 Dim lRow As Long 
 Dim strSearch As String 
 
设置wb = ThisWorkbook 
'~~ >将此设置为应用自动过滤器的工作表
设置ws = wb.Worksheets（RegEvents）
 
'~~>在组合框中的文本上过滤列1 
 strSearch = ComboBox1.Value 
 
使用ws 
'~~>删除任何过滤器
 .AutoFilterMode = False 
 
使用.Range（RegEvents_WorksheetData）
 .AutoFilter字段：= Range（RegEvents_Action）Column，Criteria1：= & strSearch 
 
'~~>标识过滤的范围
设置copyFrom = .Offset（1,0）.SpecialCells（xlCellTypeVisible）
 
'~~>将过滤的搜索中的值添加到Combobox2 
对于每个aCell在copyFrom 
 
如果aCell.Value<> and aCell.Column = Range（RegEvents_EventID）。Column Then 
 ComboBox2.AddItem（aCell.Value）
 End If 
 
如果aCell.Value& 和aCell.Column = Range（RegEvents_Event）。然后
 ComboBox2.AddItem（aCell.Value）
结束如果
 
下一个
结束于
 
'〜〜>删除任何过滤器
 .AutoFilterMode = False 
使用
结束End Sub 
 
 Private Sub UserForm_Initialize（）
'用于创建数组
 Dim listItems As Variant 
 Me.ComboBox1.ListIndex = -1'-1 =没有选择项目
 
 Label1.Caption =Action
 Label2.Caption =Event
 
 listItems = Range（CatogriesAction）
 
使用ComboBox1 
'循环遍历数组，只向组合框中添加非空白值
 For i = 1到UBound（listItems，1）
如果Len（Trim（listItems（i，1）））> 0然后
 .AddItem listItems（i，1）'填充列表框
结束如果
下一个i 
 
 .ListIndex = 0'-1 =设置为0以选择第一项
 End With 
 
'设置组合框2的列数
与Me.ComboBox2 
 .ColumnCount = 2 
 .BoundColumn = 2 
 .ColumnWidths =.5 in; .5 in
 End With 
 
 End Sub 
   .List 属性来创建多列ComboBox。填充列数据。所以在你的情况下：
 对于每个aCell在copyFrom 
 
如果aCell.Value& 和aCell.Column = Range（RegEvents_EventID）。列然后
 ComboBox2.AddItem aCell.Value 
 lRow = aCell.Row 
结束如果
 
 aCell.Row = lRow And aCell.Column = Range（RegEvents_Event）。然后
使用ComboBox2 
 .List（.ListCount  -  1，1）= aCell.Value 
 b $ b结束如果
 
下一页
  
 $ c> .TextColumn 在文本字段中显示两列。 
要求：具有ComboBox1的UserForm 
  Private Sub UserForm_Initialize（）
 
 With Me.ComboBox1 
 .ColumnCount = 3 
 .BoundColumn = 2 
 .TextColumn = 3 
 .ColumnWidths =1cm; 1cm; 0
结束于
 
 RegEvents_EventID = [{1; 2; 3; 4; 5}] 
 RegEvents_Event = [{Event 1;Event 2;Event 3;Event 4;Event 5} ] 
 
 For i = LBound（RegEvents_EventID）To UBound（RegEvents_EventID）
 With Me.ComboBox1 
 .AddItem RegEvents_EventID（i，1）
 .List（.ListCount -  1,1）= RegEvents_Event（i，1）
.List（.ListCount-1,2）= RegEvents_EventID（i，1）& & RegEvents_Event（i，1）
以
结束接下来
 
结束子
  
 
I've got two combo boxes on a user form in Excel. When I select the first combo box, it filters the rows on the current worksheet and displays the values in combobox2.

I would like combobox2 to be a multicolumn combobox so I can copy and paste multiple values from one worksheet to another but I can't get multiple values into combobox2. This is the code I've hacked together. I am able to filter the results when I change the value in combobox1 but combobox2 is displaying a single value:
Private Sub ComboBox1_Change()
Me.ComboBox2.Clear

'filter the results in combo box 2 when the user selects the different         options in combo box 1
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim copyFrom As Range, aCell As Range
    Dim lRow As Long
    Dim strSearch As String

Set wb = ThisWorkbook
'~~> Set this to the worksheet where the autofilter is applied
Set ws = wb.Worksheets("RegEvents")

'~~> Filter Column on text in Combo box 1
strSearch = ComboBox1.Value

With ws
    '~~> Remove any filters
    .AutoFilterMode = False

    With .Range("RegEvents_WorksheetData")
        .AutoFilter Field:=Range("RegEvents_Action").Column, Criteria1:="="     & strSearch

        '~~> Identify the filtered range
        Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible)

        '~~> Add values from filtered search to Combobox2
        For Each aCell In copyFrom

If aCell.Value <> "" And aCell.Column = Range("RegEvents_EventID").Column Then
ComboBox2.AddItem (aCell.Value)
End If

If aCell.Value <> "" And aCell.Column = Range("RegEvents_Event").Column Then
ComboBox2.AddItem (aCell.Value)
 End If

     Next
    End With

    '~~> Remove any filters
    .AutoFilterMode = False
End With
End Sub

Private Sub UserForm_Initialize()
'Used to create an array
Dim listItems As Variant
    Me.ComboBox1.ListIndex = -1 ' -1 = no items selected

Label1.Caption = "Action"
Label2.Caption = "Event"

listItems = Range("CatogriesAction")

With ComboBox1
'Loops through the array and only adds non blank values to the combo box
For i = 1 To UBound(listItems, 1)
 If Len(Trim(listItems(i, 1))) > 0 Then
 .AddItem listItems(i, 1) ' populate the listbox
 End If
 Next i

 .ListIndex = 0 ' -1 = no items selected, set to 0 to select the first item
 End With

'Set number of columns for combobox 2
With Me.ComboBox2
 .ColumnCount = 2
 .BoundColumn = 2
 .ColumnWidths = ".5 in; .5 in"
 End With

End Sub

解决方案
For multi column ComboBoxes you must use the .List property to fill the columns data. So in your case:
For Each aCell In copyFrom

 If aCell.Value <> "" And aCell.Column = Range("RegEvents_EventID").Column Then
  ComboBox2.AddItem aCell.Value
  lRow = aCell.Row
 End If

 If aCell.Row = lRow And aCell.Column = Range("RegEvents_Event").Column Then
  With ComboBox2
   .List(.ListCount - 1, 1) = aCell.Value
  End With
 End If

Next
A complete Example using .TextColumn displaying both columns in the text field.
Requirements: UserForm with ComboBox1
Private Sub UserForm_Initialize()

 With Me.ComboBox1
  .ColumnCount = 3
  .BoundColumn = 2
  .TextColumn = 3
  .ColumnWidths = "1cm;1cm;0"
 End With

 RegEvents_EventID = [{1;2;3;4;5}]
 RegEvents_Event = [{"Event 1";"Event 2";"Event 3";"Event 4";"Event 5"}]

 For i = LBound(RegEvents_EventID) To UBound(RegEvents_EventID)
  With Me.ComboBox1
   .AddItem RegEvents_EventID(i, 1)
   .List(.ListCount - 1, 1) = RegEvents_Event(i, 1)
   .List(.ListCount - 1, 2) = RegEvents_EventID(i, 1) & " " & RegEvents_Event(i, 1)
  End With
 Next

End Sub


                        
这篇关于Excel VBA  - 将值插入到多列组合框中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！