Is it possible to set the RowSource of a combo box in VBA to incorporate multiple ranges?

[BetterBeef]

I was wondering if it is possible to set a rowsource of a combo box that I am using on a form in VBA (for Excel) using multiple ranges. For example, I would like to use the values in 'Sales Update'!A44:A51 and 'Sales Update'!A53:A63 to show up as one list in a combo box.

Is this possible or is there a way around this? I tried using Union() to bind them together, but it gave me an error message.

Any thoughts?

 

[Norie]

Sorry but the answer is no.

But I'm afraid the question is why?

What is it you are trying to do and why do you have your data stored/structured like this?

You could probably use other methods/properties to populate the control. eg AddItem, List etc

 

[BetterBeef]

The data is layed out like this because it is a report that has headings down the page. It is layed out this way because it is a report that is printed and this conserves paper space. I know how to write the code to populate on intialization of the form. Which event do I use and how do I get to that event to start coding?

 

[Norie]

In design view select UserForm from the left combobox, then select Initialize from the right combobox.

You should now see this.

Private Sub UserForm_Initialize()
 
End Sub

That's where any code you want to run when the form is opened should go.

PS I think it might be an idea to reconsider the layout of your data - it really doesn't sound conducive to analysis/reporting/exploding squirrels.

 

[BetterBeef]

And what is the code that I use to to set the combo box (cboSalesmen) to the range(s) I want to use?

 

[BetterBeef]

Nevermind, I got it. One more question, how do I close a thread after I have an answer?

 

[Norie]

You could try something like this.

Private Sub UserForm_Initialize()
Dim cl As Range
 
    For Each cl In Worksheets("Sales Update").Range("A44:A51")
    
        ComboBox2.AddItem cl.Value
        
    Next cl
    
    For Each cl In Worksheets("Sales Update").Range("A53:A63")
    
        ComboBox2.AddItem cl.Value
        
    Next cl
    
End Sub

That should work to populate the combobox but that combobox isn't going to be easy to work with.

 

[BetterBeef]

Can I use a named range from my workbook to set the combo box?

 

[Norie]

You can use a named range to populate a combobox but I'm pretty sure you couldn't use more than one named range - especially if they aren't contiguous.

Also as far as I can see using non-contiguous ranges is not a good idea, either to populate the control or work with the data.

 

[BetterBeef]

I am only using one named range. How would I set it to a name ranged?