Results 1 to 4 of 4

Check for the existence of a worksheet

This is a discussion on Check for the existence of a worksheet within the Excel Questions forums, part of the Question Forums category; Is there anyway of using VBA to check if a workbook contains a specific worksheet? Eg. if 'ABC.xls' contains the ...

  1. #1
    New Member
    Join Date
    Feb 2003
    Posts
    1

    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
    25,943

    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,330

    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

    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>

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