How can I tell if a worksheet already exists? 2

[castout]

I am creating new sheets from within VBA. First I would like to see if a sheet by that name (for example "abc123&quot already exists and if so, don't try to create "abc123" again.
Is there an easy way to do this other than looping through all sheets present?

 

[xlbo]

Don't think so but looping thru the sheets shouldn't take very long at all - in fact, even with 100 sheets, it's almost instantaneous :
Sub sheetNameTester()
Dim myTestName As String, sName As String

myTestName = "Sheet99"
For Each Sheet In ActiveWorkbook.Sheets
sName = Sheet.Name
If myTestName = sName Then
MsgBox "Name Exists"
Exit Sub
Else
End If
Next

End Sub

HTH
Geoff

 

[Loomah]

This is roughly the same as xlbo's solution but adds the sheet if it doesn't exist.

Sub test()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "abc123" Then
MsgBox "Here I am!!"
Exit Sub
End If
Next
Worksheets.Add
ActiveSheet.Name = "abc123"
End Sub

I can't think of any way to do this without looping through all the sheets

 

[amm9290]

Without looping you can do this.

function SheetExists(theName as string)

On Error GoTo DoesNotExist
Sheets(theName).Activate
sheetexists = True
On Error GoTo 0
Exit Function

DoesNotExist:
sheetexists = False
On Error GoTo 0
Exit Function

End Function

 

[xlbo]

oooh - nice function - you can have a star from me to start with ;-)
Geoff

 

[mikewoodhouse]

A similar alternative (different GoTo style) using the previous function:

Function SheetExists(theName As String) As Boolean

SheetExists = False
On Error Resume Next
Sheets(theName).Activate
If Err.Number 0 Then
SheetExists=True
End If
On Error GoTo 0

End Function

No more or less efficient, a little more concise at the expense of clarity.

 

[Loomah]

OK I lied when I said I couldn't think of a way to do this without looping but I appear to have been beaten to it!!

For the record this checks for the existence of a sheet and if it isn't there it adds it.....

Sub AddSheet()
Dim stName As String
On Error Resume Next
stName = Sheets("abc123&quot.Name

If Err.Number = 0 Then
MsgBox "Sheet Exists"
Exit Sub
End If

On Error GoTo 0
Worksheets.Add.Name = "abc123"

End Sub

I really must get a life. This came to me lying in bed last night!!!!!

Incidentally the star promised for amm9290's function hasn't appeared so I've added one (I hope). I've noticed there haven't been a lot of stars in this forum recently?

 

[xlbo]

I'm sure I gave it a star......timing ???
;-]
Geoff

 

[qleader]

Here is one I just put together. This is basically the same thing.
Enter Sheetnumber
If it exist it will go to the sheet if not then it will prompt to create it and so forth.

Dim objSheet As Worksheet
Dim strSheetnumber As String


strSheetnumber = TextBox1
If "" = strSheetnumber Then Exit Sub

'Check for existing contract
For Each objSheet In ThisWorkbook.Sheets
If 0 = StrComp(objSheet.Name, strcontract, vbTextCompare) Then

Unload Me 'unload the userform

Sheets(strcontract).Select
Exit Sub
End If
Next objSheet