Renaming Sheets with Calculate

[JonTuxIvy]

Not a code man, just trying to find some help

1. I have a worksheet named "Margin" where a user enters several names in cells B11 through B24 for different projects.
2. The following worksheets have a cell (A4:E4 Merged) that calculates its name from the appropriate cell in "Margin".
   (=Margin!B11 for example)
3. I would then like to have each worksheet name itself based on that value
4. If a relevant value is blank (ie no project name), the worksheet should hide itself.

This is my code (borrowed from several sources) but it is not functioning. Help would be appreciated.

• Possible issues... IsEmpty is not the correct command since the fomula returns "0" for a blank cell?
• The macro does not seem to be running at all...even just a name change is not renaming the tab?

Thanks for any pointers you have!

Please Login or Register  to view this content.

Added code tags...sorry about that

[Norie]

Try checking for an empty string instead of using IsEmpty.

Also, move the Exit Sub so that the next part of the code actually runs if the cell isn't empty, as it is the code will never reach the later code.

Please Login or Register  to view this content.

[JonTuxIvy]

Ok that End Sub was the issue with it not running. One problem solved.
Also got the visible and not visible working.

I still have two errors/issues.
When you enter a duplicate name the macro displays the text but stops running. I have to manually run after that.

Second is, the same thing happens on the illegal character piece.

[JonTuxIvy]

Figured out a big piece of the issue. I copied this from a worksheet_change and modified for a calculate reference cell. I had to remove the Target.ClearContents commands. It was erasing the formula in the reference cell during those error checks.

Unfortunately the worksheet name check is nor working. It only finds worksheets in front of the worksheet the macro is running on.

Any advice on that piece?

[Norie]

If you mean the check for duplicate worksheet names then that should work with all worksheets, the code doesn't specify which sheets to look at.

The problem might be where the code is located.

I'm assuming you have this code in each worksheet, if you do I'm not sure that's where it should be.

Not even sure it should be in the Calculate event, I would have thought the Change event would be more appropriate.

[zbor]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[JonTuxIvy]

Yes, its in each worksheet. I need the macro to automatically run hence I was told to put it in the worksheets as a calculate event. As to why, lets just say this will be used by people I am having to train how to use a mouse. It has to all be automatically done and Vb is new to me..so my options are limited to what I can learn in the next 48 hours.

Well i simplified the code and got rid of the

Please Login or Register  to view this content.

statements as they are bad form. But the worksheet name check is still not checking any worksheets after the active worksheet.

Relevant code

Please Login or Register  to view this content.

[Norie]

I think the reason the check appears not to be (or isn't) checking the other sheets is because you are using the Calculate event and the order of calculation.

[JonTuxIvy]

Well I figured this out this weekend. The issue was I had to use the Trim Function in order to get the tab names to properly compare.

After some testing I added some code to rename the sheets when hidden (the previous version named all hidden sheets "0" which became a loop within the Sub). I also added some code to change the value of an illegal entry to say "Enter a New Name" in the originating field. I listed the code below for others review/use in the future.

And for the record, I am pretty proud that I took others code and actually removed the error issues and expanded upon it within 1-2 days of starting. FUn stuff

Please Login or Register  to view this content.