Check for the existence of a worksheet

[llanllwni]

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.

[Joe4]

Try some code like this:

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 ]

[Richie(UK)]

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

[Nimrod]

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