excel - 组合框列表填充范围来自 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)

但是当我尝试填写 listfillrange 时:

  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