Check for the existence of a sheet named 'Sheet1' in the current workbook

Dear Experts:

I would like to check the following on the current workbook using VBA:

- Check whether  a sheet by the name 'Sheet1' exists. If not a msgbox is to say so!

Thank you very much in advance for your help.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rory ArchibaldConnect With a Mentor Commented:
DIm wks as worksheet
On error resume next
Set wks = Worksheets("SHeet1")
If err.number <> 0 then 
   msgbox "No sheet1!"
End If
On Error Goto 0

Open in new window


for example.
0
 
rspahitzConnect With a Mentor Commented:
Try this:
Sub FindSheet1()
    Dim objSheet As Worksheet
    Dim bSheetFound As Boolean
    
    bSheetFound = False
    For Each objSheet In Worksheets
        If objSheet.Name = "Sheet1" Then
            bSheetFound = True
            Exit For
        End If
    Next

    If Not bSheetFound Then
        MsgBox "Sheet1 not found!"
    End If
End Sub

Open in new window

0
 
patrickabCommented:
Or try this:
Sub Sheetfinder()
    Dim ws As Worksheet
    Dim flag as Long

    For Each ws In Worksheets
        If ws.Name = "Sheet1" Then
          MsgBox "Sheet1 found"
          flag=1
        End If
    Next
    If flag = 1 Then
        MsgBox "Sheet1 not found"
    End If
End Sub

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
patrickabConnect With a Mentor Commented:
Oops - should have been:

Sub Sheetfinder()
    Dim ws As Worksheet
    Dim flag as Long

    For Each ws In Worksheets
        If ws.Name = "Sheet1" Then
          MsgBox "Sheet1 found"
          flag=1
        End If
    Next
    If flag = 0 Then
        MsgBox "Sheet1 not found"
    End If
End Sub
0
 
Andreas HermleTeam leaderAuthor Commented:
Dear all,

thank you very much for the overwhelming support. They all seem to work just fine. I will run a couple of more tests and then let you know.

Again, thank you very much for your professional support.

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Dear all,

since all three approaches work just fine, I would suggest splitting the points evenly.

Thank you again for your great help. Regards, Andreas
0
 
patrickabCommented:
Andreas - Thanks for the points - Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.