ActiveX combo box with dynamically-chosen list

Question

I was looking at this question:How to create and populate ActiveX combo box

And I was wondering, how can be implemented when the list of items comes from a range and how to specify in which cell the list should be placed.

Morever, could be nice to have a range of values coming from two different sheets.

Sub CreateComboBox1()
    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
                Link:=False, DisplayAsIcon:=False, Left:=50, Top:=80, Width:=100, _
                Height:=15)
        With .Object
            .AddItem "Date"
            .AddItem "Player"
            .AddItem "Team"
            .AddItem "Goals"
            .AddItem "Number"
        End With
    End With
End Sub

Answer 1

I tweaked your code a bit to clean it up. Avoid using ActiveSheet and use explicit references to the worksheet you're interested in, instead. Also you should store your objects into variables:

Option Explicit

Sub CreateComboBox1()

Dim sht As Worksheet
Dim cb As ComboBox
Dim sourceRange As Range
Set sht = ThisWorkbook.Worksheets("Name of your worksheet")
Set sourceRange = sht.Range("A1:A10") 'example source range

Set cb = sht.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, Left:=50, Top:=80, Width:=100, Height:=15).Object
cb.List = sourceRange.Value

End Sub

Basically you can use the .List property to specify the source range.

Another way to do it would be to use a For-Each loop, to loop through the items you want to add to the list. This way you can add to the list, the items of two different ranges from different sheets:

Option Explicit

Sub CreateComboBox1()
Dim cell As Range
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim cb As ComboBox
Dim sourceRange1 As Range
Dim sourceRange2 As Range

Set sht1 = ThisWorkbook.Worksheets("Name of your worksheet 1")
Set sht2 = ThisWorkbook.Worksheets("Name of your worksheet 2")
Set sourceRange1 = sht1.Range("A1:A10")
Set sourceRange2 = sht2.Range("A1:A10")

Set cb = sht1.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, Left:=50, Top:=80, Width:=100, Height:=15).Object

For Each cell In sourceRange1
    cb.AddItem cell.Value
Next cell
For Each cell In sourceRange2
    cb.AddItem cell.Value
Next cell

End Sub