User Form Control having Control Source with Dynamic Range

Hard coding your data to forms is not good if you want flexibiity. The thing to do is pass the data to the form using a property procedure, and within the procedure adding it to the control.

In the form's code module:

This code accepts a variant, which in this case is an array, and assigns it to the Combo Box's list.

In the procedure which calls the form:

This code defines the array as the values in the defined range name, and sends it off to the form, then shows the form.

I have a more detailed example on my web site:
http://peltiertech.com/Excel/PropertyProcedures.html

Re: User Form Control having Control Source with Dynamic Ran

I have a Userform.
It contains a ListBox and a ComboBox and a TextBox (.enable=false)

The Listbox has a control source called CaseChoice which is a static rangename =$AO$1
The TextBox has a control source of CaseChoice as well
The ComboBox has a control source named EntryItem which is a dynamic rangename =offset(ItemChoice,0,CaseChoice)
ItemChoice is a static rangename=$AO$16.

So the CaseChoice has a value of 4, say, when I load the UserForm.
The TextBox shows 4 - good
I select the third item in the listBox
The TextBox shows 3 which is good
I change the ComboBox value.
And offset(ItemChoice,0,4) gets changed - not offset(ItemChoice,0,3) - which is bad.
So it looks like the dynamic range name is not being refreshed during the time that the UserForm is Loaded and shown when the CaseChoice cell is changed.

What am I missing or doing wrong?
How do I refresh the dynamic rangename?
Regards
Brian

Click to expand...

Brian,

Not sure if I understand completely, but you can refreshed a rowsource with something like this:

combobox1.RowSource="EntryItem" when you you make a change that affects it.

Not sure if you need to reset it first to a null. I do something similar in one of my projects where I have a default rowsource. and then when a different combo box is selected that affects it I clear the rowsource and the combo box current value and then use additem. I previously was reseting the RowSource before using the additem method which I now do.

Previously I was resetting the RowSource since the dynamic rowsource would change based on the selections of the other combos which sound similar to what you need to do.

This (additem) works for me since I have a fairly limited number of entries needed after a change is made elsewhere.

For example prior to a selection change in another combo box the combo box in question is loaded with all areas. After a selection is made this number is reduced to sub areas and can easily be handled with additem.

Perry