Source Data for Combo Boxes

**Skelly022782** · 11-02-2009, 03:33 PM

Hi

I'm new to the forums

I'm just picking up VBA now and had a question I'm hoping someone could shed some light on.

I'm trying to create a workbook for tracking costs and change orders for multiple subcontractors.

My main question is about combo boxes. I would like to create a master list of subcontractors on The masterlists tab of the attached workbook. I would like to use this masterlist as the data source for a combo box I'm going to use to replace the subcontractor txtbox on the SubCoEntryFrm.

I figured getting away from a user typing something in would benefit me if I wanted to use that subcontractor name field to analyze data

Any thoughts? I've attached the workbook to the post.

Thanks,

**jaslake** · 11-02-2009, 08:25 PM

Hi Skelly022782
It depends on how and where your ComboBox is created. I created a Dynamic Named Range called "Subcontractors" and included three different ways to view the ComboBox on your sheet Master Lists. Look behind the properties or format control to see how the Named Range is referenced.
I also added a ComboBox to your UserForm SubCoEntryFrm. Take a look at these various scenarios. If you have questions, get back to me.
John

**Skelly022782** · 11-03-2009, 01:02 PM

Thanks for your reply, I really appreciate it.

Now I understand how to create a dynamic range. I just had one followup-question. I went into the masterlists tab and added some subcontractors and noticed that the active X combo box you created didn't immediately display the newly added subs while the combo box on the form did.

When I closed the excel file and re-opened it, the direct x combo box seemed to refresh with the rest of the subs. Is this just the way a direct x combo box behaves in excel?

Thanks for the help!

**jaslake** · 11-03-2009, 02:15 PM

Hi Skelly022782
No, as you enter items into the dynamic range, they should be available immediately to the ComboBox. I think what might be happening is that the examples in my post had only 8 items in the examples and thats all you were seeing after you added additional items. You would need to scroll down to see your added items.
You can fix this by changing the properties of the ComboBox. It's called "List Rows" in the Activex form and "Drop Down Lines" in the Form Control. Try changing those items to a greater number and see if this doesn't fix the problem. You can make the number anything you want.
Let me know how you make out. John

**Skelly022782** · 11-03-2009, 04:56 PM

Made out great. Thanks again.

**jaslake** · 11-03-2009, 09:04 PM

You're welcome. If you're satisfied with the solution, please mark your thread solved.
To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save
John