PDA

View Full Version : Solved: Check if Sheet Name Already Exists



akamax_power
05-27-2009, 12:13 PM
I have a UserForm that allows you to create a new worksheet by copying an existing sheet and renaming it to the value entered in the UserForm. The problem is if the worksheet name already exists it errors out so I need a way to check if the worksheet name exists before adding the sheet with a message box to notify you before allowing the code to continue. Here's my code so far:

Private Sub Add_Click()

If Category.Value = "" Or Phase.Value = "" Then
MsgBox "You must enter a value for Category and Phase before continuing", vbOKOnly

Dim catname, phsname As String

'Values from UserForm
catname = Category.Value
phsname = Phase.Value

'Template sheet to copy from
Sheets("CatTpl").Copy after:=Sheets(shtins)

'Rename new sheet automatically created by copy
Sheets("CatTpl (2)").Name = catname


Sheets(catname).Visible = True

mdmackillop
05-27-2009, 12:16 PM
Dim sh As Worksheet
On Error Resume Next
Set sh = Sheets(CatName)
If Not sh Is Nothing Then
MsgBox "Sheet Exists"
Exit Sub
End If
On Error GoTo 0

akamax_power
05-27-2009, 02:26 PM
Perfect, thanks