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?
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?
Rory Archibald
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Very Much Appreciate your knowledge and insight to this problem.
Ron
Ron
FYI, Tin's method is what I said. ;)
Using List, you just use:
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.
Using List, you just use:
Listbox1.list = Range("whatever").valueIf 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.
ASKER
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...
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...
You can't bind a control "through Name Manager", so I assume you just mean that it's bound to a named range?
ASKER
Yes, its bound to: LogTable[Column1] on Sheet3.