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). <pre>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</pre>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