Create new sheet error

Drmwvr7266

Board Regular
Joined
Apr 7, 2002
Messages
164
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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
 

Forum statistics

Threads
1,147,621
Messages
5,742,184
Members
423,710
Latest member
Duarte85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top