Userform textbox names worksheet, already exists and errors out

[TimPslim]

Okay, I have a sheet where you scan some barcodes and then press a button.
Once the button is pressed, a bunch of macros start and ultimately the end result is a pivot table on a new worksheet. Once the table is created on the new worksheet, the final step is a UserForm that pops up and tells the user to name the worksheet.
As I'm testing the workbook before it goes live, I've come to the realization that someone may try to name the new pivot table worksheet a name that is already taken. I tested it and it errors.
Basically, I'm looking for the code, or help finding the code, to add to my code to keep this from happening.

Here's my code as it sits - the textbox used to name the new sheet is named "custdate"

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox "You MUST Enter a Name"
Cancel = True
End If
End Sub
Private Sub CommandButton1_Click()
If Trim(Me.custdate.Value) = "" Then
    Me.custdate.SetFocus
    MsgBox "You MUST Enter a Name"
    Exit Sub
End If
    ActiveSheet.NAME = Me.custdate.Text
    ActiveSheet.Move after:=Worksheets(Worksheets.Count)
    Me.custdate.Value = ""
    Me.custdate.SetFocus
    Unload Me
End Function

 

[VoG]

One way

End If
    On Error Resume Next
    ActiveSheet.Name = Me.custdate.Text
    On Error GoTo 0
    ActiveSheet.Move after:=Worksheets(Worksheets.Count)
    Me.custdate.Value = ""
    Me.custdate.SetFocus
    Unload Me
End Function

 

[TimPslim]

When I try that, it gives me

Compile Error:
Unexpected End Sub

"End Function" is highlighted, and the
"Private Sub CommandButton1_Click()" is yellow in the debugger screen.

Is there a way to make it pop up an error box saying "Try Again" or something instead?

 

[taurean]

Because the last line should be:

End Sub

and not

End Function

 

[VoG]

This would be the complete code

Private Sub CommandButton1_Click()
If Trim(Me.custdate.Value) = "" Then
    Me.custdate.SetFocus
    MsgBox "You MUST Enter a Name"
    Exit Sub
End If
    On Error Resume Next
    ActiveSheet.Name = Me.custdate.Text
    On Error GoTo 0
    ActiveSheet.Move after:=Worksheets(Worksheets.Count)
    Me.custdate.Value = ""
    Me.custdate.SetFocus
    Unload Me
End Function

Alternatively you can use this function to check if a worksheet exists

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

 

[TimPslim]

Awesome, End Sub was the problem.
Thanks for the help!