Stopping duplicate worksheets from being created

[Matt0110]

Hi all,

I have the following code to produce a copy of a template worksheet and promting the user to give it a name.

Private Sub CommandButton1_Click()
ActiveWorkbook.Unprotect
If Me.TextBox1.Value = "" Then
Beep
MsgBox "Please give your location a name"
TextBox1.SetFocus
Else
    Sheets("Template").Visible = True
    Sheets("Template").Copy Before:=Sheets(1)
    ActiveSheet.Name = TextBox1.Value
    Sheets("Template").Visible = False
 
    ActiveWorkbook.Protect
    Unload Location
 
End If
 
 
End Sub

However, as you may have guessed, there's no error control and the user can insert a name that already exisits!

Ideally, i'd like a warning to appear explaining that they've tired to create a duplicate but also promting the user to change the name in the textbox refered to above.

Any points would welcome!

Kind regards,

Matt

 

[Anthony47]

Try modifying this
TextBox1.SetFocus
Else
Sheets("Template").Visible = True

to this:

Retry:
TextBox1.SetFocus
Else
Alreadin=0
    For Each ws In Worksheets
    If  TextBox1.Value = ws.Name then Already=1
    Next ws
    If Alreadin > 0 then 
Msgbox("The selected name already exists, retry") : goto Retry
    Sheets("Template").Visible = True

Bye.