Selecting a dynamic name

[mikelee101]

Hello,
I'm looking for a relatively easy way to select dynamic named ranges. I
know that you can type the name into the name box, but I don't always
remember exactly what the name is.
My thought was to build a form and populate a combo or list box with the
names from the active workbook. However, I can't seem to figure out a way to
set the rowsource property to anything other than a range of cells on the
active sheet. Is there a way that I can accomplish something like the
following:

Me.cmbNamesList.RowSource = ActiveWorkbook.Names

The closest I've gotten is to add a worksheet, add the names to a range on
the sheet, set the rowsource to that range, collect the value from the form,
then delete the sheet. However, that's not terribly elegant, and it requires
figuring out what sheet the range is on and programmatically activating it
before attempting to select the range, which adds another layer of problems
(at least, for me). So I figured I'd see if anyone had any ideas.

Of course, if there's an addon out there anywhere that puts the names in the
name box that I haven't been able to find, that'd be even better.

Thanks to all for the help.

 

[Matthew Pfluger]

Oh, I get to make someone's day! You need Jan Karel Pieterse's Name Manager.
Go to http://www.jkp-ads.com/officemarketplacenm-en.asp.

This is one of the best Excel add-ins available. In my opinion, this tool
should have been bundled with Excel 2007. I was just talking to Jan about
that this morning.

HTH,
Matthew Pfluger