+ Reply to Thread
Results 1 to 7 of 7

Prevent Errors for Duplicate Tab Names When Naming Tabs Using Cell Reference

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    15

    Prevent Errors for Duplicate Tab Names When Naming Tabs Using Cell Reference

    Hello,

    I have been looking through the forum for a while and I have not seen a clear response to this here or anywhere.

    By way of background; I have a workbook that copies a template worksheet (using a macro button) and the user inputs their first and last name. It then formats the name using a first initial underscore last name scheme (i.e. J_Doe), It is this abbreviation that is then used to name the tab. The issue I run into, is there may be a John and Jane Doe, so I would like for the macro to identify when this error is going to occur and append a number to the back end of the users abbreviated name (i.e. J_Doe1). My current script achieves this, BUT it only allows for one duplication before an error occurs. I am looking for the macro to identify when the error is going to occur again and assign the next entry to J_Doe3 when naming the book. How can this be achieved, please see my script below.

    Please Login or Register  to view this content.
    Thank you!

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Prevent Errors for Duplicate Tab Names When Naming Tabs Using Cell Reference

    A possibility without using error trap

    Please Login or Register  to view this content.
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Prevent Errors for Duplicate Tab Names When Naming Tabs Using Cell Reference

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-04-2013
    Location
    Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Prevent Errors for Duplicate Tab Names When Naming Tabs Using Cell Reference

    This is great! Thank you so much. I ended up going with PCI's response. That one did exactly what I needed it to do. I am fairly new to VBA and I can get some basics done, but I rely a lot on these forums for help. Now that I see how you approached this, I am learning more about some different functions etc. Much appreciated!!!

  5. #5
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Prevent Errors for Duplicate Tab Names When Naming Tabs Using Cell Reference

    Just to close the loop and do code properly, here the last modif.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-04-2013
    Location
    Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Prevent Errors for Duplicate Tab Names When Naming Tabs Using Cell Reference

    This revised code does not work, the previous code you posted worked better. The only thing it does that is funny, is every time a cell changes it toggles the name of the sheet from (example: J_Doe to J_Doe1 and then back to J_Doe). It must be determining that the name already exists within itself and making the change. I should be able to work around that buy changing the macro to run when cell B4 is updated, rather than when the sheet is calculated.

    If you can let me know what the new code does that the old code did not, I would like to hear it.

    Thanks!

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Prevent Errors for Duplicate Tab Names When Naming Tabs Using Cell Reference

    The main change in the new code was to use

    Please Login or Register  to view this content.
    to exit immediately from the sub if cell "B4" is not concerned
    We should do the same for

    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. Automaticaaly Naming Sheets/Tabs from Cell
    By wdlyons in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-14-2012, 09:35 PM
  2. Auto naming with CutePDF and cell names
    By Birdster in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-11-2012, 04:55 AM
  3. Replies: 0
    Last Post: 08-01-2012, 11:03 AM
  4. Replies: 2
    Last Post: 07-15-2006, 07:55 AM
  5. Replies: 2
    Last Post: 05-05-2006, 06:25 PM

Tags for this Thread

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