Create new sheet error

[Drmwvr7266]

How do I set up VB to create a new sheet, but if its name is the same as an existing name, loop until existing sheet names do not equal new sheet name.

Here is what I have tried so far:

Sub Macro2()

On Error GoTo Wrong
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
UserForm1.Show
ActiveSheet.Name = UserForm1.TextBox1.Value
UserForm1.TextBox1 = ClearContents
Exit Sub
Wrong:

MsgBox "You have entered a name that is already in use. Try again.", vbOKOnly + vbCritical, "Naming error"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
UserForm1.TextBox1 = ClearContents
TryAgain

End Sub


The TRYAGAIN code loops me back into Macro2. The trouble is, every time there is an incorrect answer, it loops until a new name is given. There is no way to cancel out. Ideas? Suggestions?

Can I add code to the msgbox (VBOKCancel) to exit the sub? Or how about another macro to exit on the userform? The userform presently has two commandbuttons. CB1 closes the userform, CB2 does nothing.
This message was edited by Drmwvr7266 on 2002-08-29 13:36
This message was edited by Drmwvr7266 on 2002-08-29 13:48

 

[XL-Dennis]

Drmwvr7266,

<pre>
Sub test()
Dim lnReply As Long

lnReply = MsgBox("......", vbOKCancel)

If lnReply = 2 Then
Exit Sub
Else
'Your code......
End If
End Sub
</pre>

See the directhelp in XL too

Kind regards,
Dennis