Defining the list filll range for a combo box?

[mike ryan]

How do I go about defining the list fill range for a combo box?

I have created 2 list of named ranges on a worksheet I have called "Hidden Validations", yet when I add a combo box to my user form, the list fill range option isn't even showing under properties? It's just going from ControlSource to ListRows?

Any ideas at all please?

Many Thanks

[majosum]

If you right-click on the combo box, select "Format Control". I'm using Excel 2007, so it may be worded differently. On the "Control" tab in the dialog box that appears, there is a box for "Input Range:". This is where you enter the named range or the exact range of the values you want to include in the drop-down list.

[stnkynts]

@majosum. I could be wrong but I believe the OP is talking about a combobox as part of a userform which is different than a form control combobox

@mike ryan. You will need to populate the combobox on initialization of the userform. Let's say your named range is "Mike" then something like this would do.

Please Login or Register  to view this content.

[mike ryan]

@stnkynts That is exactly what I am hoping to do. I hope you don't mind but I copied your code (adjusting the defined range name) but get the following error : "Run-time error '1004': Method ' Range' of object'_Global'failed. Any thoughts?

[stnkynts]

Hmm. I can only guess without having a copy of the workbook but I would guess that you have multiple sheets and the auto sheet reference is selecting the wrong sheet. Example: your range "Mike" is on Sheet 2 but you run the code when the active sheet is Sheet 1. It will try to find "Mike" in Sheet 1, which doesn't exist. Try specifying the Sheet and see if that helps;

Please Login or Register  to view this content.