+ Reply to Thread
Results 1 to 9 of 9

Renaming Sheets with Calculate

  1. #1
    Registered User
    Join Date
    03-19-2015
    Location
    US
    MS-Off Ver
    Multiple
    Posts
    5

    Renaming Sheets with Calculate

    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
    Last edited by JonTuxIvy; 03-20-2015 at 04:13 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Renaming Sheets with Calculate

    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.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    03-19-2015
    Location
    US
    MS-Off Ver
    Multiple
    Posts
    5

    Re: Renaming Sheets with Calculate

    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.

  4. #4
    Registered User
    Join Date
    03-19-2015
    Location
    US
    MS-Off Ver
    Multiple
    Posts
    5

    Re: Renaming Sheets with Calculate

    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?

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Renaming Sheets with Calculate

    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.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,111

    Re: Renaming Sheets with Calculate

    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)
    Never use Merged Cells in Excel

  7. #7
    Registered User
    Join Date
    03-19-2015
    Location
    US
    MS-Off Ver
    Multiple
    Posts
    5

    Re: Renaming Sheets with Calculate

    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.
    Last edited by JonTuxIvy; 03-20-2015 at 04:18 PM.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Renaming Sheets with Calculate

    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.

  9. #9
    Registered User
    Join Date
    03-19-2015
    Location
    US
    MS-Off Ver
    Multiple
    Posts
    5

    Re: Renaming Sheets with Calculate

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Renaming Sheets 1 to 31
    By greveg in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-02-2013, 06:46 AM
  2. Renaming Sheets
    By Melissa9 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 09-12-2012, 12:52 PM
  3. Renaming Sheets
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2008, 03:25 PM
  4. Renaming sheets
    By Peter1999 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-20-2007, 10:29 AM
  5. renaming sheets
    By Kjeldc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-29-2006, 07:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1