Help Creating a Dynamic ListFillRange for ActiveX ComboBox

[RunTime91]

Let me begin by saying I'm not use to working with Embedded ActiveX Controls ~

I have an Embedded ComboBox on Sheet1 and I'm trying to fill it with a dynamic list from Sheet2.

I have tried using every method I have for Userform ComboBoxes and nothing. I have Googled, Bing'd, watched 7 YouTube videos and consulted Walkenbach ~ Nothing.

Please tell me this is possible and if so, please guide me on how to accomplish this ~

Below are just some of my efforts so far, each one producing either nothing or a 'Type Mismatch' Error:

For Each Cell In [List]  'List is a Named Range using the offset/counta arguments
    Me.ComboBox1.AddItem Cell
Next Cell

Dim LastRow As Long
LastRow = ThisWorkbook.Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

Rng1 = Thisworkbook.sheets("Sheet2").Range("A1:A" & LastRow)

Me.ComboBox1.ListFillRange = Rng1

Rng1 = Range("List").Address(0,0)
Me.ComboBox1.ListFillRange = Rng1

As always ~ Thank You Very Much

 

[JoeMo]

I avoid active-x controls in favor of form controls wherever possible so can't be much help. This works for me provided the named range "List" is on the same worksheet as the active-x combobox:

Private Sub ComboBox1_DropButt*******()
ComboBox1.ListFillRange = Range("List").Address
End Sub

EDIT: Replace the asterisks in the code box with "on Click" (no quote marks and remove the space after "on") - not sure why the asterisks are appearing there.

 

[RunTime91]

I avoid active-x controls in favor of form controls wherever possible so can't be much help. This works for me provided the named range "List" is on the same worksheet as the active-x combobox:

Private Sub ComboBox1_DropButt*******()
ComboBox1.ListFillRange = Range("List").Address
End Sub

EDIT: Replace the asterisks in the code box with "on Click" (no quote marks and remove the space after "on") - not sure why the asterisks are appearing there.

Joe ~ Thank You for chiming in ~ I'm looking for something that works between sheets. I'm convinced ActiveX Controls simply can't work in that fashion ~ Looks I'm going back to UserForms and UserForm Controls.

Thanks again, Joe ~

 

[JoeMo]

Joe ~ Thank You for chiming in ~ I'm looking for something that works between sheets. I'm convinced ActiveX Controls simply can't work in that fashion ~ Looks I'm going back to UserForms and UserForm Controls.

Thanks again, Joe ~

Good move. You are welcome.