Setting ListFillRange through VBA

[dennismhancy]

I have a combo box called ComboBox1 on a worksheet. I want to populate
its values in VBA code.

Let's say my list of values is in J2:J12.



I then issue this command: ComboBox1.ListFillRange = "J2:J12", I get
a "Run-time error '424'; Object required" message


If I change this to: Set Worksheets(1).ComboBox1.ListFillRange =
"J2:J12", I get "Run-time error '438'; Object doesn't support this
property or method."



Tried several other combinations, but nothing works yet.

Can anyone help? Thanks.



Dennis Hancy
Eaton Corporation
Cleveland, OH

 

[NickHK]

If you are running code from the same WS that contains ComboBox1 you first
code will work.
If not, your second code will work, but without the "Set", as you are not
'setting' an object variable.

NickHK

 

[Tom Ogilvy]

Worksheets(1).ComboBox1.ListFillRange ="J2:J12"

but I would include the sheet name as well

With Worksheets(1)
.ComboBox1.ListFillRange = .Name & "!J2:J12"
End with

if it is on Worksheets(1)

 

[dennismhancy]

Thanks Tom..

Looks like I forgot the ".name" piece. Once I put that in, it worked
great.



Dennis