Determine if a sheet exists in a workbook
1999-12-20 Workbooks 0 194
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 FunctionExample:
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 FunctionExample:
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