Combobox listfillrange from 2 differents sheets

Question

I would like to fill a combobox with data from 2 differents sheets.

I have that function to create 2 ranges which works:

    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)

But then when i try to fill the listfillrange :

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

• Union doesn't work between sheets
• I need to use ActiveX combo box (because they are writable)

Answer 1

I highly recommend an approach using For-Each like the one Jvdv has demonstrated as it is easy, intuitive and elegant.

If however, you absolutely have to do it your way (combining two lists into one), then here's my suggestion:

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 

Finally if you want to programmatically create an ActiveX combobox in worksheet sht3 for example and assign it with lst3 you have to do it like so:

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

UPDATE

JvdV's version modified to work with an ActiveX combobox would look like so:

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