• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

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
0
AndreasHermle
Asked:
AndreasHermle
3 Solutions
 
Rory ArchibaldCommented:
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
 
rspahitzCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
patrickabCommented:
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
 
AndreasHermleAuthor 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
 
AndreasHermleAuthor 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now