Check for the existence of a worksheet

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Check for the existence of a worksheet

  1. #1
    New Member
    Join Date
    Feb 2003
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Is there anyway of using VBA to check if a workbook contains a specific worksheet? Eg. if 'ABC.xls' contains the worksheet 'Sheet2'

    Appreciate any help or guidance.

    Thank you.


  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,672
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default

    Try some code like this:

    Code:
    Sub CheckSheet()
    
        On Error GoTo Err_check
        Sheets("Sheet2").Select
        
        Exit Sub
        
    Err_check:
        If Err.Number = 9 Then
            MsgBox "No such sheet exits"
            Resume Next
        Else
            MsgBox Err.Number & " " & Err.Description
        End If
        
    End Sub
    Obviously, replace "Sheet2" with whatever sheet name you are looking for.

    [ This Message was edited by: jmiskey on 2003-02-11 08:35 ]

  3. #3
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You might like to include the test in a function. That way you can make use of it as and when you need to (it also helps to make your code easier to maintain).
    Sub test()
    Dim shtName As String
    shtName = "Sheet2"
    MsgBox "Does " & shtName & " exist? : " & WorksheetExists(shtName)
    End Sub

    Function WorksheetExists(WorksheetName As String) As Boolean
    Dim sht As Object
    On Error Resume Next
    Set sht = ActiveWorkbook.Sheets(WorksheetName)
    WorksheetExists = (Err.Number = 0)
    End Function
    HTH
    Richie

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Here's a solution that will also tell you what visible value the sheet has ... if not found the function Returns "N/A"

    Function SheetState(shName) As String
    ' will find all 3 states of sheets and report
    On Error GoTo No
    Select Case Sheets(shName).Visible
    Case 0: SheetState = "Hidden"
    Case -1: SheetState = "VeryHidden"
    Case 2: SheetState = "Visible"
    End Select
    Exit Function
    No: SheetState = "N/A"
    End Function
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com