Assign range name based on cell values

Doing something like that, you may have more than one name for the same range. I added a routine to delete it.

Without seeing all of your code, I can only guess why you had problems. Try this routine. Modify the other ranges as I did for the first one.

Obviously, the Change event is inserted into your sheet by right clicking the sheet tab, View Code and paste. The DeleteBook() can be in that section or a Module.

Please Login or Register  to view this content.

Thanks for the reply. There are some parts of your code that I do not understand. I would appreciate some clarification on those points:

1) What is the purpose of the line Range("A3").Value = "Test". To me that looks like you are setting the value of A3, and A3 is a cell where the teacher enters the value.

2) Do I place this code in front of all the named ranges:

Please Login or Register  to view this content.

3) When I put the deletebookmark code in front of each range, does that say it will delete that if a new value is entered in A11 and then create the new name with the new value? Am I understanding that part correctly?

Just for clarification, I am calling the naming from he entering in of A11-A18 since those are entered after A3-A6, but A3-A6 are also values that the teacher enters names into.

Then the name range is the combination of a value in the A3-A6 range and the A11-A18 range.

The Change event fires everytime a change is made in any cell on that sheet. While we could have used Intersect() to only execute parts of the Change events code for a certain set of cells, Case Select is going to work for your needs. IF() would have worked as well. I prefer Case Select since it only executes for the first case that is True.

Typically, one uses Intersect() and then exit the sub if the intesection range is Nothing.

So, when A11 or A32 is changed, then your Case code executes.

I set a value in A3 to "Test" just so the code that creates the Name would have a valid name. You should understand that building a Name must be valid. e.g. If A3 and A11 had values but they were deleted. The Change event fires and it tries to set the Name = "". Obviously, you can't do that.

(2) Yes, put it before each line where you are adding a name. Otherwise, multiple changes would result in multiple Names that all referto the same range of cells.

Okay I am going to test this code,and let you know how it goes.

Just FYI The cells will never be empty or deleted. Teachers can change the values entered in those cells, but they can not delete them, so it should be okay.

Last question all of this is happening on the set-up worksheet which sets-up the entire program.

Right now that page runs extremely slow. It takes forever upon entering a value in a cell before you can enter into another cell. I know it is not due to the millions of cells that are referenced by it, but by the VBA code. I took away the vba code, but left all the cell references, and it was very fast.

On the set-up page I have code to hide rows based on cell values and then another naming code, and then this naming code.

Is there a way to cause the page to run faster?

Yes, that code works beautifully!!!

I love the addition of deleting the old named range. Great work!!

I realized I should start a new thread about how slow the set-up page is running, and I will do so after I get the code all complete.

Thanks for help with the name ranges coding!