Dynamic Named Ranges do not show up in the "Name Box"

[pecosvil]

I just created a Dynamic Defined Named Range following this references:
http://www.ozgrid.com/Excel/DynamicRanges.htm
http://support.microsoft.com/kb/830287

It works perfectly, except for the fact that the Named Range is not appearing in the "Name Box". The curious thing is that the named range is working fine. For example, if I created a Dynamic Named Range named "Currencies" and I go to the "Name Box" and type "Currencies" it will automatically select the appropriate range. But if I just go to the "Name Box" and try to find the range "Currencies" I won't be able to find it.

Anyone experienced anything similar? Any ideas of what's happening or how can I solve it?

Many thanks in advance!

 

[Fazza]

If I understand correctly, your concern is users should not have to run subroutines.

The subroutine suggested would run automatically when the users moves off the worksheet with the lists. If I understand correctly, that overcomes the concern.

regards

 

[pecosvil]

Thanks Fazza!
Quick question. How is it that the subroutine will run automatically when the users moves off the worksheets? I have "fairly ok" programming skills (basically, in Stata), and am new in VBA. I thought that Subroutines need to be called (either by running the subroutine, or called by other subroutine) in order to be ran - on the other hand, I understand that Functions (UDFs) are called whenever you call the UDF in the spreadsheet =UDFexample() -

Thansk for your help!

 

[Jonmo1]

Thanks Fazza!
Quick question. How is it that the subroutine will run automatically when the users moves off the worksheets?
Thansk for your help!

Now there's a loaded question....

You're about to dive into some real cool stuff.


They're called Events.

Excel tracks all kinds of events, and you can run code upon any of those events being "triggered"

Events like
Activating/Deactivating a Sheet
Activating/Deactivating a Book
Changing which cell is currently selected (moving from one cell to another0
Changing the Value of a cell
Formula recalculating
all kinds of things.

Here's a an example

In a new book with at least 2 sheets, named Sheet1 and Sheet2
Right click on the tab for Sheet1 and paste the following

Private Sub Worksheet_Activate()
MsgBox "Hello, you just triggered the Sheet Activate Code for Sheet1"
End Sub
Private Sub Worksheet_Deactivate()
MsgBox "Hello, you just triggered the Sheet DeActivate Code for Sheet1"
End Sub

Now switching between sheet1 and sheet2

Also notice in the Sheet1 module, there are 2 dropdowns on the top of the module.
The one on the right lists all the event codes available for the sheet.

Hope that helps.

 

[pecosvil]

Many thanks! Indeed, that's pretty cool stuff!

 

[Fazza]

some info from a great site, http://www.cpearson.com/excel/Events.aspx

 

[gclearmonth]

Hi everyone,

Assuming this thread isn't active anymore but just in case anyone is searching, wanted to add my two cents.

I recently had this problem where I had created one worksheet and named about ten ranges (ie. selected the cells and typed in a name in the Name Box). The name of the worksheet was "Q1" as I was running data quarterly. I wanted to do the same thing for Q2, Q3 and Q4 so I figured I could just copy over the Q1 tab three times, and go into Name Manager and manually edit the named ranges to Q2, Q3 and Q4 as appropriate. This shortcut didn't end up working though and after some investigation, I noticed that even though the named ranges appeared in Name Manager, they did not appear in the Name Box dropdown listing. Therefore, any attempt to reference these ranges in formulas resulted in a #NAME error.

Unfortunately I never found a workaround and just did it the long way which, for only 30 extra ranges didn't take me very long, but if I had to do the same thing for for monthly or weekly tracking, it would start to get to be too much work. Anyone have a fix for this? I assume it can be corrected with a macro, but I'm hoping it's just a settings problem. Thanks!

 

[mariahcarey]

This thread is pretty old, I know... but what's the point of having this dynamic option? then, when a formula seeks for this name and since it won't be found in the range list, it retuns a #ref ! error...
There's always a piece missing in Excel, that's so frustrating...

 

[My Aswer Is This]

The best way I have of building a dynamic named range which will also show up in the Name Box above Range A1 is to do this:

Create yourself a Table Give it any name you want.
And Presto you have a Dynamic named range.
You can remove the table formatting if you want and hide the Filter buttons if you want.

As you add to the table the named range changes.

 

[mariahcarey]

The best way I have of building a dynamic named range which will also show up in the Name Box above Range A1 is to do this:

Create yourself a Table Give it any name you want.
And Presto you have a Dynamic named range.
You can remove the table formatting if you want and hide the Filter buttons if you want.

As you add to the table the named range changes.

that is actually pretty clever

 

[shg]

... but what's the point of having this dynamic option? then, when a formula seeks for this name and since it won't be found in the range list, it returns a #ref ! error...

The fact that they don't appear in the Names box doesn't affect their use in formulas -- they work fine.

Excel has its limitations, but that's not one of them.