I have a combobox on a sheet and I want to update its list with non numerical
values organised on another sheet in a row.

THIS WORKS:

ActiveSheet.OLEObjects("Combobox1").ListFillRange =
Range("SubRegion1").Address


BUT THIS DOES NOT WORK

ActiveSheet.OLEObjects("Combobox1").ListFillRange =
Range("SubRegion1").Address
Set myrange = Sheets("Define Regions").Range("SubRegionsList")
For Each c In myrange
ActiveSheet.OLEObjects("Combobox1").AddItem c.Value
Next
THE ERROR is 435 (object does not support this property method
Combobox1.AddItem c.Value does not work either

Any idea?
I call the sub when I activate the sheet
Any help greatly appreciated
--
caroline

Hi,

Try

Set Myrange = Sheets("Define Regions").Range("SubRegionsList")
For Each c In Myrange
ActiveSheet.OLEObjects("Combobox1").Object.AddItem c.Value
Next

Mike

"caroline" wrote:

I have a combobox on a sheet and I want to update its list with non numerical
values organised on another sheet in a row.

THIS WORKS:

ActiveSheet.OLEObjects("Combobox1").ListFillRange =
Range("SubRegion1").Address


BUT THIS DOES NOT WORK

ActiveSheet.OLEObjects("Combobox1").ListFillRange =
Range("SubRegion1").Address
Set myrange = Sheets("Define Regions").Range("SubRegionsList")
For Each c In myrange
ActiveSheet.OLEObjects("Combobox1").AddItem c.Value
Next
THE ERROR is 435 (object does not support this property method
Combobox1.AddItem c.Value does not work either

Any idea?
I call the sub when I activate the sheet
Any help greatly appreciated
--
caroline

HI Mike,
Thanks for that, but I now have a "run time error 70 permission denied"
error message, when I run the code
Any idea?
--
caroline


"Mike H" wrote:

Hi,

Try

Set Myrange = Sheets("Define Regions").Range("SubRegionsList")
For Each c In Myrange
ActiveSheet.OLEObjects("Combobox1").Object.AddItem c.Value
Next

Mike

"caroline" wrote:

I have a combobox on a sheet and I want to update its list with non numerical
values organised on another sheet in a row.

THIS WORKS:

ActiveSheet.OLEObjects("Combobox1").ListFillRange =
Range("SubRegion1").Address


BUT THIS DOES NOT WORK

ActiveSheet.OLEObjects("Combobox1").ListFillRange =
Range("SubRegion1").Address
Set myrange = Sheets("Define Regions").Range("SubRegionsList")
For Each c In myrange
ActiveSheet.OLEObjects("Combobox1").AddItem c.Value
Next
THE ERROR is 435 (object does not support this property method
Combobox1.AddItem c.Value does not work either

Any idea?
I call the sub when I activate the sheet
Any help greatly appreciated
--
caroline

Caroline,

You are getting this becase the ListfillRange property of the combobox is
set to a range. Put the combobox in design mode by clicking the icon on the
toolbar and right click the combo box and view propertiies. Clear out the
range setting for the listfillRange range. exit design mode and the code I
gave you will work.

Mike

"caroline" wrote:

HI Mike,
Thanks for that, but I now have a "run time error 70 permission denied"
error message, when I run the code
Any idea?
--
caroline


"Mike H" wrote:

Hi,

Try

Set Myrange = Sheets("Define Regions").Range("SubRegionsList")
For Each c In Myrange
ActiveSheet.OLEObjects("Combobox1").Object.AddItem c.Value
Next

Mike

"caroline" wrote:

I have a combobox on a sheet and I want to update its list with non numerical
values organised on another sheet in a row.

THIS WORKS:

ActiveSheet.OLEObjects("Combobox1").ListFillRange =
Range("SubRegion1").Address


BUT THIS DOES NOT WORK

ActiveSheet.OLEObjects("Combobox1").ListFillRange =
Range("SubRegion1").Address
Set myrange = Sheets("Define Regions").Range("SubRegionsList")
For Each c In myrange
ActiveSheet.OLEObjects("Combobox1").AddItem c.Value
Next
THE ERROR is 435 (object does not support this property method
Combobox1.AddItem c.Value does not work either

Any idea?
I call the sub when I activate the sheet
Any help greatly appreciated
--
caroline