Check for sheet name

  • Thread starter Thread starter Mike Fogleman
  • Start date Start date
M

Mike Fogleman

How would I check to see if there is a sheet named "Sheet1" in the active
workbook.

Sub CheckFor New Sheet()

If sheets("Sheet1") does not exist in this WB Then
MsgBox ("No sheet has been added")
Exit Sub
Else
End If

continue...

End Sub
 
Mike

What you need to do is to loop through the worksheets collection,
while looking at each worksheets name. The following code should serve
as an example

Sub FindASheet()

' Local Variables
Dim wks As Worksheet
Dim blnWksPresent As Boolean

For Each wks In Application.Worksheets
If wks.Name = "Feed Sheet (1)" Then
blnWksPresent = True
End If
Next wks
If Not blnWksPresent Then Exit Sub

End Sub
 
Mike,

Here's another method:

Sub test()

Dim sh As Worksheet

On Error Resume Next
Set sh = ActiveWorkbook.Worksheets("Sheet1")
If Err <> 0 Then
MsgBox "no Sheet1"
Else
MsgBox "Sheet1 exists"
End If
On Error GoTo 0

End Sub

hth,

Doug
 
Chip gave this as an answer once I thought it was about as short as
possible.

Function WorksheetExists(WSName As String, Optional WB As Workbook =
Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook,
WB).Worksheets(WSName).Name))
End Function
 
Thanks guys, a loop worked fine because there can only be 1 or 2 sheets
total in the book.
 
Back
Top