How can I adjust this code to accommodate duplicates?

[figuare9]

So basically, I click on a button, some things pop up, and it names my sheets for me. However, I'm having a bit of a hard time trying to get it check to see if there's already a duplicate sheet name.

Right now, it works perfectly, until I create two worksheets with the same name. Would anyone be willing to provide a bit of help to set this up so that it cancels itself if there's already a duplicate sheet name? Preferably with a pop up window that says "This sheet name is already used. Please try again." I'd really appreciate a bit of help on this! I can do beginner stuff, but it quickly got out of my hands when I wanted to search for duplicates

Thanks!

Private Sub New_Sheet_Click()Dim NewName As String
Dim Soldto As String
Dim Shipto As String
Dim OrderNumber As String
Dim OrderDate As String


'InputBox Job #


NewName = InputBox("Enter The Job #" & vbCrLf & vbCrLf & "(Note: Must not be blank to proceed)")
If NewName = vbNullString Then
    Exit Sub
        End If


'InputBox Order #


OrderNumber = InputBox("Enter Order #" & vbCrLf & vbCrLf & "(Note: Must not be blank to proceed)")
If OrderNumber = vbNullString Then
    Exit Sub
        End If
        
'InputBox Order Date:


OrderDate = InputBox("Enter Order Date" & vbCrLf & vbCrLf & "(Note: Must not be blank to proceed)")
If OrderDate = vbNullString Then
    Exit Sub
        End If
        
'InputBox Sold To:


Soldto = InputBox("Enter Sold To" & vbCrLf & vbCrLf & "(Note: Must not be blank to proceed)")
If Soldto = vbNullString Then
    Exit Sub
        End If
        
'InputBox Ship To:


Shipto = InputBox("Enter Ship to" & vbCrLf & vbCrLf & "(Note: Must not be blank to proceed)")
If Shipto = vbNullString Then
    Exit Sub
        End If




Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(2)


ActiveSheet.Name = NewName
ActiveSheet.Range("L4") = NewName


ActiveSheet.Range("C7") = Soldto
ActiveSheet.Range("K7") = Shipto
ActiveSheet.Range("L5") = OrderNumber
ActiveSheet.Range("L3") = OrderDate
ActiveSheet.Range("L3") = OrderDate
ActiveSheet.Shapes("TextBox 23").Delete
ActiveSheet.Shapes("Arrow: Up 24").Delete
ActiveSheet.Range("D12").Select
ActiveSheet.Shapes("TextBox 1").Delete


End Sub

 

[Fluff]

How about

   NewName = InputBox("Enter The Job #" & vbCrLf & vbCrLf & "(Note: Must not be blank to proceed)")
   If NewName = vbNullString Then
      Exit Sub
   ElseIf Evaluate("isref('" & NewName & "'!a1)") Then
      MsgBox "sheetname already exists"
      Exit Sub
   End If

 

[figuare9]

Thanks for the reply! Worked Perfectly! It didn't quite work at first but I noticed I had the code on the wrong sheets. I really appreciate the help!

 

[Fluff]

Considering I cannot see my code in the code you just posted, I'm not surprised it didn't work.

 

[figuare9]

Oh, no your code worked perfectly! I had already typed up it didn't work when you replied. But I edited my post too soon. I really appreciate the help! thanks again!!!

 

[Fluff]

You're welcome & thanks for the feedback