Setting ListFillRange through VBA
 

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

Setting ListFillRange through VBA
 

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

"
egroups.com...
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

Setting ListFillRange through VBA
 

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)

--
Regards,
Tom Ogilvy


" wrote:

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

Setting ListFillRange through VBA
 

Thanks Tom..

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



Dennis


Tom Ogilvy wrote:
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)

--
Regards,
Tom Ogilvy


" wrote:

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