Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Smile Code to prevent Duplicate Worksheet Names

    Experts,

    I have a file where the user enters data, then enters a date in cell I1 before archiving it to a new worksheet. If they do that each month before long there will many worksheets.

    I am trying to find code that will prevent duplicate tab names by using a message box to warn the user to use another name.

    I found the following code on Google, but it only list the existing worksheet names.

    Sub ListSheetNames()
    Dim R As Range
    Dim WS As Worksheet
    Set R = ActiveCell
    For Each WS In ThisWorkbook.Worksheets
    R.Value = WS.Name
    Set R = R(2, 1)
    Next WS
    End Sub

    Thanks in advance for any ideas.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    Here's some code that will trap the Insertion of a new worksheet and prompt for the sheet name. It will then check it against the existing sheet names and not allow it if it already exists.
    Code:
    Option Explicit
    
    Private Sub Workbook_NewSheet(ByVal shtNew As Object)
     
       Dim zNewShtName As String
       Dim zChkSht     As Worksheet
       
       Do
         zNewShtName = _
            Trim(InputBox("Enter the desired name for the new worksheet", _
                          "New Worksheet Name:"))
       
         For Each zChkSht In ActiveWorkbook.Sheets
            If UCase(Trim(zChkSht.Name)) = UCase(zNewShtName) Then
              MsgBox "Sheet Name: " & zNewShtName & " is already in use!" & _
                     vbCrLf & vbCrLf & "Please enter a new name...", _
                     vbOKOnly + vbCritical, _
                     "Duplicate Worksheet Name:"
              zNewShtName = ""
              Exit For
            End If
         Next zChkSht
         
       Loop Until zNewShtName <> ""
       
       shtNew.Name = zNewShtName
       
        shtNew.Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        
    End Sub  'Workbook_NewSheet
    Place this code in the ThisWorkbook module in the VBA editor.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG-

    Thank you for the code. I haven't been able to make it work yet.

    If you have time to help, I attached the file.

    Thanks
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    Ok I've worked it over a bit.

    You couldn't get my code to work because you were creating the new sheet via a copy which does NOT trigger the Workbook_Change event. From your description I thought the user was doing it manually. So I've integrated my code into your Archive routine. I've also made a few changes there like having the button call Archive directly and having Archive call the User Form (this could also be done with an InputBox statement as in my original code).

    Check out the code in the attached modified copy of your code and let me know what you think.

    Modified File: Financial_AssetsFiver4.xlsm

    BTW: You might want to consider a Cancel button on your user form and code to exit the routine if selected...always give em' a way out!

    HTH
    Last edited by RetiredGeek; 2015-07-21 at 16:11.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2015-07-21)

  6. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG-

    Wow! That is nice. I'm surprised Excel doesn't have a less involved way to deal with this issue.

    Thank you for taking the time to integrate this into the archive code.

    I will add the cancel button.

    I will work the code. So far, works like a charm.

  7. #6
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG-

    I took the code you provided and used it in an expanded Assets Worksheet I've been working on to learn Excel. It seems to me it should have worked as it was in the file you worked on, but for some unknown reasons, at least for me, it isn't working as well.

    I've run into a few problems that are over my head. If you have time, please check out the expanded file attached.

    The file works OK until I attempt to duplicate a date then it hangs up.

    Also, there are two buttons on the Asset sheet. One of them move to the new sheet when archived.
    Attached Files Attached Files

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    Ok there were a couple of problems. Mostly with the copying of the sheet and that sheet becoming the ActiveSheet causing problems with the duplicated range names. I've added the cancel button and logic to handle it and also left some comments explaining why I did some things. HTH

    Test file: Financial_Assets-WS-2.xlsm

    Post back if you have questions.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Try something based on:
    Code:
    Sub Demo()
    MsgBox SheetExists(ActiveWorkbook, InputBox("What is the test Sheet name?"))
    End Sub
    
    Function SheetExists(xlWkBk As Workbook, xlWkShtNm As String) As Boolean
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(xlWkBk.Sheets(xlWkShtNm).Name) > 0 Then SheetExists = True
    NoSuchSheet:
    End Function
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. The Following 2 Users Say Thank You to macropod For This Useful Post:

    Excelnewbie (2015-07-22),RetiredGeek (2015-07-22)

  11. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    Excellent Idea!

    E.N.,

    Here's the latest code with Paul's excellent suggestion incorporated.

    Latest Version: Financial_Assets-WS-3.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2015-07-22)

  13. #10
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG and Paul-

    Thank you for the leg up! (leg up definition: assistance over a high object)

    As a newbie to VBA I certainly appreciate the help I've received from those who frequent this forum.

    I've purchased books, taken online classes at Udemy.com, and wearing out Google searches to learn what I can of VBA. With all that, the best resource has been this forum, hands down.

    I'll study the codes and take all I can away and hopefully make more progress.


Posting Permissions

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