Compare New Sheet Name to current sheets

[edwilke86]

Trying to do something that should be simple, I'm just not familiar with VBA syntax. I'm decent with C++, Python, and LUA, but I don't know about VBA. I have a command button that prompts the user for a name, then creates a sheet based on a template sheet with that name. That works fine.

Private Sub AddNewScout_Click()

Dim NewName As String
NewName = InputBox("Name of Scout: (Last, First)")


Worksheets("Template").Copy Before:=Worksheets("Template")
Worksheets("Template (2)").Name = NewName


End Sub

It has a problem, if there is already a sheet with the same name, an error is generated, and a new sheet named "Template (2)" is created. I don't want my user filling the book up with redundant templates, obviously. So what I'm trying to do is this, though obviously this isn't correct.

if NewName = AnyCurrentSheet Name then
MsgBox("Error: Name Exists")
// then break

else
Worksheets("Template").Copy Before:=Worksheets("Template")
Worksheets("Template (2)").Name = NewName

end if [code]

I just need to know the syntax to compare NewName to all the current sheet names, so I can break if it matches and not create a sheet at all. My Google-Fu has failed me for hours. 

Thank you!

[I]Using Office 365, also. [/I]

 

[edwilke86]

Second bit of code should look like

[COLOR=#333333]if NewName = AnyCurrentSheet Name then[/COLOR]
[COLOR=#333333]MsgBox("Error: Name Exists")[/COLOR]
[COLOR=#333333]// then break[/COLOR]

[COLOR=#333333]else[/COLOR]
[COLOR=#333333]Worksheets("Template").Copy Before:=Worksheets("Template")[/COLOR]
[COLOR=#333333]Worksheets("Template (2)").Name = NewName[/COLOR]

[COLOR=#333333]end if[/COLOR]

 

[Fluff]

Hi & welcome to the board
Try

Private Sub AddNewScout_Click()

   Dim NewName As String
   NewName = InputBox("Name of Scout: (Last, First)")
   If Evaluate("isref('" & NewName & "'!A1)") Then
      MsgBox "That name already exists"
      Exit Sub
   End If

   Worksheets("Template").Copy Before:=Worksheets("Template")
   Worksheets("Template (2)").Name = NewName


End Sub

 

[edwilke86]

Thank you! That works!

 

[Fluff]

You're welcome & thanks for the feedback

 

[My Aswer Is This]

I read your code and it does work.
But I do not understand how this line of code can see if this sheet name already exist.
Would you please explain
If Evaluate("isref('" & NewName & "'!A1)") Then

You're welcome & thanks for the feedback

 

[Fluff]

ISREF is a worksheet function that checks if a value is a valid reference & returns either TRUE or FALSE.
so if you enter =ISREF(Sheet1!A1) into a cell it will return TRUE if there is a sheet called Sheet1, or FALSE if not. (The A1 can be any valid cell reference)
The Evaluate function calculates a worksheet formula in VBA, so these two lines do the same thing

Sub chk()
Debug.Print WorksheetFunction.Sum(Range("A1:A5"))
Debug.Print Evaluate("sum(A1:A5)")
End Sub

For some reason you cannot use WorksheetFunction.isref

 

[My Aswer Is This]

Thanks for that answer I entered nothing in A1 and the script determined somehow that the sheet already existed. Glad to see it works even if I dont understand. You explained it well but my brain cannot evaluate it.

Also never knew about:

Worksheets("Template ​

(2)")​

.Name = NewName

I always used Activesheet.name=NewName

I learned 2 new tricks today.

​

ISREF is a worksheet function that checks if a value is a valid reference & returns either TRUE or FALSE.
so if you enter =ISREF(Sheet1!A1) into a cell it will return TRUE if there is a sheet called Sheet1, or FALSE if not. (The A1 can be any valid cell reference)
The Evaluate function calculates a worksheet formula in VBA, so these two lines do the same thing

Sub chk()
Debug.Print WorksheetFunction.Sum(Range("A1:A5"))
Debug.Print Evaluate("sum(A1:A5)")
End Sub

For some reason you cannot use WorksheetFunction.isref