UserForm ComboBox & RowSource

I have a UserForm in which RowSource is a Defined Name from a Table Column (IDX).  I can enter the Defined Name:   nSht3IDXArray and the ComboBox initially accepts the Values (1,2,3,,,,).

When a new row, with the next numerical sequence, is entered, the Defined Name variable will not update or refresh for sometime.  At some point it does, but haven't quite figured out what sequence in needed for this to occur.  Maybe it's closing the UserForm and reopening it, but there would have to be another method to refresh a Defined Name for a control on a UserForm.  I have even tried refreshing the Defined Name, but it seems to have no effect on the ComboBox.

Has anyone experienced this with Excel 2007 or with Defined Names used as a RowSource?
Cook09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Normally, as best I recall, you have to clear and reset the RowSource property. I never use it (always use .List), so I may be wrong.
0
TinTombStoneCommented:
If your combobox uses a named range as its source.
When new items are added to the list, the named range must be resized to incorporate the new item, then the source property of the combobox needs to be updated.

There does not seem to be a Refresh/Requery property for Excel ComboBoxes, however the following works

    Me.cboBox.RowSource = ""
   
    Me.cboBox.RowSource = "=NamedRange"

I have attached an example workbook for you
 Combo-Example.xlsm Combo-Example.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cook09Author Commented:
Tin,
Your method worked perfectly. It updated as soon as I entered another number and ran the Name Update Routine.  With Excel Tables, there is no need to resize as it does it automatically.  However, it does have to go through an initialization process.

Rory:
I tried several different ways with the List, or Text, or Value, but since Name Manager already had them in a semicolon array, it just didn't seem to take.  RowSource must compensate for a string such as that, or at least Name Manager 2007.

Through this process, there is a definite difference between Name Manager 2003 and 2007.  It really seems to be  much more versatile and easier to use than Name Manager 2003.  I use it now to store my LastRows, LastColumns, all Constants, and any variables that may need to be Public.  Unlike Public variables, it holds its value, come errors or high water, which also means less declaring of variables for each mod.  Although, they still need to put into Name Manager, but I have a routine that I can run to do that.  It took a while to figure a lot of it out, and this was one of those.

Thanks for the inputs.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Cook09Author Commented:
Very Much Appreciate your knowledge and insight to this problem.

Ron
0
Rory ArchibaldCommented:
FYI, Tin's method is what I said. ;)

Using List, you just use:
Listbox1.list = Range("whatever").value

Open in new window


If you bind controls to sheets, you will often find that your control events are triggered when you don't expect it, which is why I never do it.
0
Cook09Author Commented:
Yes, some of the controls on the UserForm  are bound, but through Name Manager; which does get its values from a sheet.  You are also correct that even being separate, or indirectly connected, it sometimes has a mind of it's own.  I've had to put safeguards in when it begins to fire off, but this is what they wanted.

I just tried how you presented it and it also worked...sorry.  I guess no sleep for a day or two gets one a little slow.

Cheers...
0
Rory ArchibaldCommented:
You can't bind a control "through Name Manager", so I assume you just mean that it's bound to a named range?
0
Cook09Author Commented:
Yes, its bound to:   LogTable[Column1]  on Sheet3.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.