I copied that last subroutine from a previous post. mySfx isn't required in
your case.

Toss that earlier version and replace it with:

Sub GiveItANiceName(myPFX As String, wks As Worksheet)

Dim iCtr As Long
Dim myStr As String

Do
If iCtr = 0 Then
myStr = ""
Else
myStr = " (" & iCtr & ")"
End If
On Error Resume Next
wks.Name = myPFX & myStr
If Err.Number < 0 Then
Err.Clear
Else
Exit Do
End If
On Error GoTo 0
iCtr = iCtr + 1
Loop

End Sub





Dave Peterson wrote:

I didn't use a userform, but this worked with a simple InputBox:

Option Explicit
Sub testme01()

Dim TestSht As Object
Dim UserShtName As String
Dim OkToAdd As Boolean
Dim resp As Long
Dim wks As Worksheet

UserShtName = InputBox("Enter name")

OkToAdd = False
If SheetExists(UserShtName) = False Then
'doesn't exist
OkToAdd = True
Else
'match upper/lower case of existing sheet name
UserShtName = Sheets(UserShtName).Name
resp = MsgBox("That name already exists." & _
vbLf & "Want to make another?", Buttons:=vbYesNo)
If resp = vbNo Then
'don't change oktoadd
Else
OkToAdd = True
End If
End If

If OkToAdd = True Then
Set wks = Worksheets.Add
Call GiveItANiceName(UserShtName, wks)
End If

End Sub
Function SheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0)
End Function
Sub GiveItANiceName(myPFX As String, wks As Worksheet)

Dim iCtr As Long
Dim mySFX As String
Dim myStr As String
Do
If iCtr = 0 Then
myStr = ""
Else
myStr = " (" & iCtr & ")"
End If
On Error Resume Next
wks.Name = myPFX & mySFX & myStr
If Err.Number < 0 Then
Err.Clear
Else
Exit Do
End If
On Error GoTo 0
iCtr = iCtr + 1
Loop
End Sub

jnf40 wrote:

Hi all,

I have a workbook that adds worksheets, names them and sorts them. The
naming is done by a cell entry from a userform. I would like to check for a
worksheet with the same name as the entry from the userform then ask the user
if they want to add another worksheet with that name or activate the last
worksheet made with that name. As in another post I have, the worksheets
could be DBL ARROW for worksheet1 name, if the user enters DBL ARROW again
then it would ask them if they want another worksheet with the same name, if
they do it would create DBL ARROW (2), if the user enters DBL ARROW again
then it would ask them if they want another worksheet with the same name, if
they do it would create DBL ARROW (3), if they do not want another with the
same name then they could activate the last one made, which was DBL ARROW
(2), or they could cancel and not create another worksheet with that name. Is
there a way to do this?

--

Dave Peterson

--

Dave Peterson