• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Change comboxbox list

Hiya,
I am trying to change the listfillrange on combobox 1, but alas the code does not seem to work. I would like it change on the way in to excel but for now I am just trying to get the macro to work. Sadly I am getting an 'object required' message....


Code:
Sub ComboBox1_Change()
ComboBox1.ListFillRange = "list"
End Sub


Thanks for any assistance



-CL
 
Hi ,

You need to pass a range , as follows :

Combobox1.ListFillRange = "A1:A10"

If you have defined a named range called list , where list refers to a worksheet range , then your code will work correctly.

However , you should not change a combobox ListFillRange from within a combobox change event procedure , since each time a selection is made within the combobox , the ListFillRange will be changed.

Narayan
 
Hi ,

You need to pass a range , as follows :

Combobox1.ListFillRange = "A1:A10"

If you have defined a named range called list , where list refers to a worksheet range , then your code will work correctly.

However , you should not change a combobox ListFillRange from within a combobox change event procedure , since each time a selection is made within the combobox , the ListFillRange will be changed.

Narayan

Thank you.

And I agree re procedures. It will be set to only fire once upon opening the workbook. This is to overcome the Excel 2013 bug where it loses the combobox properties.

Sadly a dynamic or static range will not work. Its definitely called 'combobox1'. The name 'list' is dynamic based on an offset formula which is working as desired.

Help?


Thanks,
 
It will be set to only fire once upon opening the workbook.
So where is this code? If it's in the ThisWorkbook code-module, Combobox1 will need to be qualified with a sheet id:
Sheet2.ComboBox1.ListFillRange = "list"
or
Sheets("Sheet2").ComboBox1.ListFillRange = "list"
(adjusted for your actual sheet codename/name of course).
 
How and where is your combobox used? Is it on UserForm or Worksheet? If on worksheet is it ActiveX or FormControl?

Also note that ".ListFillRange" will have issues reading dynamic named range (static one will work fine).

Instead use ".List" property and fill it with value like below (example for UserForm combobox).
Code:
ComboBox1.List = Range("list").Value

EDIT: Just remembered workaround for using dynamic named range with ".ListFillRange".
First set up dynamic named range using Table column. Then in named range formula, use "=INDIRECT(TableName[TableColumn])". Then add ActiveX Combobox to worksheet.

Code would be something like...
Code:
Dim cmbbx As OLEObject

Set cmbbx = Worksheets("Sheet1").OLEObjects("ComboBox1")
cmbbx.ListFillRange = "list"
 
Last edited:
Here. I found a file where I used Data Validation list in conjunction with ActiveX Combobox (using ".ListFillRange") to build dynamic dependent dropdown list (using method found in link below).
http://www.contextures.com/xlDataVal02.html

Note that ComboBox is hidden, until a cell with Data Validation is clicked. Code is in Sheet2 ("Data") module.
 

Attachments

  • Example.xlsb
    183.4 KB · Views: 40
Last edited:
Back
Top