|
Combobox
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 |
Combobox
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 |
Combobox
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 |
Combobox
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 |
| All times are GMT +1. The time now is 02:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com