Determine if a sheet exists in a workbook

 1999-12-20    Workbooks    0    53

The function below can be used to determine if a sheet exists in a workbook:

Function SheetExists(strSheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error Resume Next
    SheetExists = Len(Sheets(strSheetName).Name) > 0
    On Error GoTo 0
End Function
Example:
If Not SheetExists("MySheetName") Then
    MsgBox "MySheetName doesn't exist!"
Else
    Sheets("MySheetName").Activate
End If

The function below can be used to determine if a sheet exists in a specific workbook:
Function SheetExists(wb As Workbook, strSheetName As String) As Boolean
' returns TRUE if the sheet exists in wb
    SheetExists = False
    On Error Resume Next
    SheetExists = Len(wb.Sheets(strSheetName).Name) > 0
    On Error GoTo 0
End Function
Example:
If Not SheetExists(ThisWorkbook, "MySheetName") Then
    MsgBox "MySheetName doesn't exist in " & ThisWorkbook.Name
Else
    MsgBox "MySheetName exist in " & ThisWorkbook.Name
End If

If Not SheetExists(Workbooks("WorkbookName.xls"), "MySheetName") Then
    MsgBox "MySheetName doesn't exist!"
Else
    MsgBox "MySheetName exist!"
End If


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.