Defined names referring to nonexistent range won't pick up once existing

**nofzinger** · 02-17-2010, 04:26 PM

Sorry for the confusing title; I was trying to fit it all in there.

I have created 500+ defined names that refer to worksheets that do not exist (yet). When I add the previously nonexistent worksheets, the defined names that refer to them are not "live" and do not work in functions where that name is used. If you go into the defined names and click on the ones that refer to the now existent worksheet, they start to work, but I was hoping there was a way around this step (e.g. a simple macro that says "update all defined names").

I created all the names so that the person who will eventually be using this will only have to add an appropriately named worksheet and put the data in...then everything will automatically work. I do not think adding names is difficult, but the person who will be using this does, so I want to avoid that person mucking around in my names.

Any help would be greatly appreciated. Thanks!

**rwgrietveld** · 02-17-2010, 04:58 PM

This can be done using a simple macro.

My question only is:
The names refer to something that is not there yet. That's fine as long as the name is not used as then excel will give you a open workbook dialog box.

Next: Where is the check that the reference is valid once the worksheets with the proper name and the proper ranges are "active"?

Why not write a routine to add names once all worksheets are in the workbook?

**nofzinger** · 02-17-2010, 05:18 PM

Well, you bring up some good points. The names are used, but I don't get an open workbook dialog. I should note that all the names are listed in a column as text, but are referred to in formulas by indirect(). Not sure if that sheds any light.

The worksheets are all named similarly (01-2010, 02-2010, etc....to 01-2040 and back to 01-1997) and the defined names that relate to them are all ACT012010, ACT022010, etc. I have a macro that creates all 517 names and if you just run that macro when the worksheet is there, it picks it up, but the macro takes a while, so it's not a good solution. I made a macro that is the same, but just uses todays date, goes back half a year, and creates 2 years worth of defined names. So, whoever enters the current data will just click that shorter macro and hopefully the page she enters is 6 months ago to 1.5 years later (e.g. if she puts in 02-2010 in 04-2010, it will add all names from 10-2009 to 10-2011 and will pick up the just added sheet). That is my current workaround and should work for all intended purposes, but certainly not the most efficient way to go about this.

Ideally, it would look at worksheets that start with a number and add the three defined names correctly for those worksheets, but i'm not that good.

**nofzinger** · 02-17-2010, 05:27 PM

Here is the code to initially define all names (thanks to someone on this site)

Please Login or Register  to view this content.

Here is the code to just go back 6 months and then populate the following 2 years

Please Login or Register  to view this content.

Hopefully this helps explain what i'm trying to do. Thanks.

**rwgrietveld** · 02-18-2010, 04:34 AM

What you could do is use events for this action.

HTML Code:

The Userform code (1 Textbox1 and 1 CommandButton1) will then:
1) Set the name of the New Sheet
2) Set all the names corresponding to the Sheet

HTML Code:

Open the atachement and press Shift F11 (Add New Sheet)
Note that the SheetName must be unique !

**nofzinger** · 02-18-2010, 10:36 AM

Your worksheet works really well. How would I import all those things into my spreadsheet? And what is an event?

**rwgrietveld** · 02-18-2010, 10:49 AM

An event is a trigger mechanism build in Excel as a standard.

When you open a workbook, The workbook_open event is triggered.
By default there is no code, so it does nothing.
You can add code.

There are many events. Most used ones are
Workbook_Open
Worksheet_Change
Selection_Change
and more.

In your case I recommend that when adding a new sheet, Workbook_NewSheet is triggered and you'll have fll controll on it's new name.

This is done with a form, but it could be that you don't ask the user but change the name according your own scheme.

Where to start with your Workbook ?!
That fully depends on how you are going to use it.

Please specify what the user actions are starting from a template file.

**nofzinger** · 02-18-2010, 10:52 AM

It looks like you've got what I want fully figured out. I'm now just having trouble getting your stuff into my worksheet. I just copied the userform into my sheet, but it doesn't automatically run when I add a new sheet.

**rwgrietveld** · 02-18-2010, 11:12 AM

This code

Please Login or Register  to view this content.

Is the trigger that calls the userform.

When you are in the VBE (editor) you'll see the useform. Above it there is a ThisWorkbook (blank sheet with an excel icon on top). Double click it and past the above code there.