VBA to populate combo box with defined name range

[KP_SoCal]

Hi all, I have a combo box ActiveX control on my excel worksheet that I would like to have it's selection list populated with a Named Range that I have defined.

Under normal circumstances, I would just use Excel's data validation option to create assign my list to a cell. Of course I would specify it to ignore blank cells since I'm using a Dynamic Named Range. In this case there would be no need for an ActiveX control.

Alternatively, I could do this relatively simply through the form control as well. However, in both of these choices, I'm limited because I need to change the Font Size. I can easily do this by changing the font properties in the ActiveX control, but don't see a way possible to do this for form controls or in embedded cell list.

So my question, is there a way to change the font through some VBA code for a form control combo box? If not, is their a VBA function that I can assign to my ActiveX control combo box to populate its selection list with one of my defined Named Ranges? Thanks so much for any feedback on this!!!

KP

 

[Nalani]

is their a VBA function that I can assign to my ActiveX control combo box to populate its selection list with one of my defined Named Ranges? {/quote]

Is this what you are looking for?

Sub WorkSheet_Activate()
 
Worksheets(1).OLEObjects("ComboBox1").ListFillRange = "Data"
ComboBox1.ListIndex = 0
 
End Sub

 

[KP_SoCal]

Hi Nalani, thanks for the quick response. When I run it, I get a run-time error of "Unable to get the OLEObjects property of the Worksheet class."

I'm just a begginer with VBA, but I think the problem has something to do with the "Worksheets(1)". Any ideas?

 

[Nalani]

Did you put the code in Sheet1's module? If not chnge the (1) to what ever sheet you CB is on.

 

[Nalani]

Also see this similar post for Named ranges.

http://www.mrexcel.com/forum/showthread.php?t=457266

You may have to adjust.

 

[KP_SoCal]

Did you put the code in Sheet1's module? If not chnge the (1) to what ever sheet you CB is on.

Yes, I put it into Sheet 1's module, but I'm still getting the error message. However, when I remove "Worksheets(1)" from the beginning of the code, it works perfectly.

Sub WorkSheet_Activate()
 
OLEObjects("ComboBox1").ListFillRange = "vehRange"
ComboBox1.ListIndex = 0
 
End Sub