Link to home
Create AccountLog in
Avatar of Cook09
Cook09Flag for United States of America

asked on

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?
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of TinTombStone
TinTombStone

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Cook09

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.
Avatar of Cook09

ASKER

Very Much Appreciate your knowledge and insight to this problem.

Ron
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.
Avatar of Cook09

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...
You can't bind a control "through Name Manager", so I assume you just mean that it's bound to a named range?
Avatar of Cook09

ASKER

Yes, its bound to:   LogTable[Column1]  on Sheet3.