组合框列表填充范围从 2 个不同的工作表

Question

我想用来自 2 个不同工作表的数据填充一个组合框。

我有这个功能来创建 2 个有效的范围：

    Dim lst as Range
    Dim lst2 as Range
    Set sht1 = ThisWorkbook.Worksheets("BaseAliments")
    Set sht2 = ThisWorkbook.Worksheets("BaseRecettes")
    Set lst = Range(sht1.Range("A1").address, sht1.Range("A" & sht1.Rows.Count).End(xlUp).address)
    Set lst2 = Range(sht2.Range("A1").address, sht2.Range("A" & sht2.Rows.Count).End(xlUp).address)

但是当我尝试填充列表填充范围时：

  Set Ctrl =Worksheets("Menu").OLEObjects.Add(ClassType:="Forms.ComboBox.1")
  With Ctrl
      .Name = "CB"
      '.ListFillRange = 'Something to concatenate lst and lst2
  End With

• 并集在工作表之间不起作用
• 我需要使用 ActiveX 组合框(因为它们是可写的(

Answer 1

Union根本不适用于不同的工作表。我认为您最好的选择是单独添加这些项目：

Set Ctrl = ActiveSheet.Shapes.AddFormControl(xlDropDown, Left:=Cells(1, 1).Left, Top:=Cells(2, 1).Top, Width:=100, Height:=20)
With Ctrl
    .Name = "CB"
    For Each cell In lst
        .ControlFormat.AddItem cell.Value
    Next cell
    For Each cell In lst2
        .ControlFormat.AddItem cell.Value
    Next cell
End With

Answer 2

我强烈推荐一种使用For-Each的方法，例如 Jvdv 已经展示的方法，因为它简单、直观和优雅。

但是，如果您绝对必须按照自己的方式进行操作(将两个列表合并为一个(，那么这是我的建议：

Sub initializeCombo()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim sht3 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim lst1 As Variant
Dim lst2 As Variant
Dim lst3 As Variant
Dim i As Long
Set sht1 = ThisWorkbook.Worksheets("WS1") 'The worksheet where the first list is
Set sht2 = ThisWorkbook.Worksheets("WS2") 'The worksheet where the second list is
Set sht3 = ThisWorkbook.Worksheets("WS3") 'The worksheet where the ComboBoxt is
Set rng1 = sht1.Range(sht1.Range("A1"), sht1.Range("A" & sht1.Rows.Count).End(xlUp))
Set rng2 = sht2.Range(sht2.Range("A1"), sht2.Range("A" & sht2.Rows.Count).End(xlUp))
lst1 = rng1
lst2 = rng2
''''''''''''Combine the 2 lists into one'''''''''''''
ReDim lst3(1 To UBound(lst1) + UBound(lst2))        '
For i = LBound(lst1) To UBound(lst1) Step 1         '
    lst3(i) = lst1(i, 1)                            '
Next i                                              '
For i = UBound(lst1) + 1 To UBound(lst3) Step 1     '
    lst3(i) = lst2(i - UBound(lst1), 1)             '
Next i                                              '
'''''''''''''''''''''''''''''''''''''''''''''''''''''
sht3.OLEObjects("ComboBox1").Object.List() = lst3
End Sub 

最后，如果您想以编程方式在工作表 sht3 中创建一个ActiveX组合框并为其分配lst3您必须这样做：

    Dim Ctrl As Object
    Set Ctrl = sht3.OLEObjects.Add(ClassType:="Forms.ComboBox.1").Object
    Ctrl.List() = lst3

更新

JvdV的版本修改为与ActiveX组合框一起使用，如下所示：

Sub initializeCombo2()
Dim sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
Dim lst1 As Range, lst2 As Range, cell As Range
Dim Ctrl As Object
Set sht1 = ThisWorkbook.Worksheets("BaseAliments") 'The worksheet where the first list is
Set sht2 = ThisWorkbook.Worksheets("BaseRecettes") 'The worksheet where the second list is
Set sht3 = ThisWorkbook.Worksheets("Menu") 'The worksheet where the ComboBoxt is
Set lst1 = sht1.Range(sht1.Range("A1"), sht1.Range("A" & sht1.Rows.Count).End(xlUp))
Set lst2 = sht2.Range(sht2.Range("A1"), sht2.Range("A" & sht2.Rows.Count).End(xlUp))
Set Ctrl = sht3.OLEObjects.Add(ClassType:="Forms.ComboBox.1").Object
For Each cell In lst1
    Ctrl.AddItem cell.Value
Next cell
For Each cell In lst2
    Ctrl.AddItem cell.Value
Next cell
End Sub

Answer 3

我以一种简单的方式成功地在 ComboBox 中填充了 ListFillRange 属性。我有一个名为"设备"的选项卡，其中包含租赁成本表。 我选择了要使用的范围，并在工作表的单元格引用字段中将其命名为"LIST1"。然后，我填充了组合框(在另一个选项卡上(LIST1 的 ListFillRange 属性。 做。 对于不同的组合框，我有几个不同的列表填充范围，所以我的命名约定是 LIST1、LIST2 等。