Finding worksheet

[jnf40]

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?

 

[Peter Mounsey]

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?

For i=1 to worksheets.count
if sheets(i).name=<NEW NAME> then
if msgbox("A sheet with this name exists, do you want to use that
sheet"),vbYesNo)=vbNo then
'Add new sheet
exit for
else
sheets(i).activate
exit for
endif
endif
next
if i > worksheets.count then
'Add sheet
endif


Pete

 

[Dave Peterson]

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

 

[Dave Peterson]

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

 

[jnf40]

Thanks Dave, once again you came through with flying colors...could you also
look at another post I have it is under "Filling Cells", your help is greatly
appreciated.

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

 

[jnf40]

Dave another question about this...if the user chooses no what would be the
code to pick the worksheet with the largest iCtr...say they had sheets named
ARROW...ARROW (2)... and ARROW (3)...and they chose No it would
automatically activate the ARROW (3) sheet.

 

[Dave Peterson]

If you have Arrow, Arrow (1), Arrow (2), Arrow (3), Arrow (9), this will find
that missing "arrow (4)" and stop there. It works ok if there are no gaps in
names.

Option Explicit
Sub testme01()

Dim TestSht As Object
Dim UserShtName As String
Dim OkToAdd As Boolean
Dim resp As Long
Dim wks As Worksheet
Dim LargestSheet 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
Set LargestSheet = FindLargestSheet(UserShtName)
If LargestSheet Is Nothing Then
MsgBox "Error--this shouldn't happen"
Else
LargestSheet.Activate
End If
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 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
Function FindLargestSheet(myPFX As String) As Worksheet

Dim iCtr As Long
Dim SheetToUse As Worksheet
Dim myStr As String

Set SheetToUse = Nothing
iCtr = 0
Do
If iCtr = 0 Then
myStr = ""
Else
myStr = " (" & iCtr & ")"
End If

If SheetExists(myPFX & myStr) Then
Set SheetToUse = Sheets(myPFX & myStr)
'and keep looking
Else
'get out
Exit Do
End If

iCtr = iCtr + 1
Loop

Set FindLargestSheet = SheetToUse

End Function

==============
If you have gaps in names, then maybe you could estimate the largest (#) used
and just count backwards until one is found. Then use that.