Excel Geeking: RowSource For A Control In A Modeless Userform

I know that the title of this post is going to sound like a Charlie Brown adult talking to most casual readers. But what I really want in a blog post title is something that is going to really tell me what is inside so that I don’t waste my time. Which is what I strove for above. Sorry if it’s too geeky.

This one is a short one, and one I needed last week. It’s one I already know, but always forget about. Forgot about it last week and then the userform didn’t open properly, and I spent five minutes stepping through the code trying to figure out what I did wrong, only to find it was the RowSource property.

So what’s the problem? Well, as any Excel VBA programmer can tell you, you can set certain userform controls to read their contents from a range. This is an alternative to adding every single item you want manually. It applies most often to the combo box control and the listbox control. It might apply to the listview control (which is a really nifty userform control, but can be a little hard to work with), but I didn’t stop writing this long enough to check.

How it works is that, in the properties for one of these controls is a property called “RowSource”. All you need to do is add the name of the range to this property and all of the items in the range are added for you. One of the benefits of this approach is that, as additional items are added to the range, the items are also added to the control (presuming you’re using a dynamic range of some type). It doesn’t require the range itself as a variable, just a string of the range name.

What does this look like? Like this:

Userform1.Combobox1.RowSource = [String of the range name]

Great! Beautiful! Flawless!

Except…

What if your userform happens to be modeless? (For the uninitiated, a modeless userform means that, when the form is running, you’re not locked into using the form the whole form and nothing but the form until you close it. You can click around on worksheets or even across workbooks. If you want a native Excel demo of this, open up the Find/Replace form and click back into the worksheet while the form is still open.)

If your userform is a modeless one, then the RowSource approach above won’t work as it is. What happens is that Excel doesn’t know where the range is, since being modeless means the scope of workbooks you can access while the form is running is bigger than just that workbook.

So what’s the answer? You need to tell the control which workbook contains that range that is acting as your RowSource. That looks something like this:

Userform1.Combobox1.RowSource = "'" & ThisWorkbook.Name & "'!" _
                                & [String of the range name]

By adding the name of the workbook to the total RowSource string, you’re pointing the control to a very specific place to get the data. And you’re making it so that your form will, you know, function.

As a final thought, I always add the apostrophe in the front and back of the workbook name. This allows the workbook to have a name that is not one long string of unbroken by spaces. If you don’t have spaces in your file name, then you technically don’t need them. But if you save the file under a new name and that name has spaces, BANG! you get an error. And who wants that? Not I.