Check Whether a Sheet in an Open Workbook Exists

This function requires that the workbook(s) it checks be open. It returns True if the sheet is found and False if it is not: SheetExists(SName, WBName)

The arguments are

SName—The name of the sheet being searched.

WBName—Optional, the name of the workbook containing the sheet.

Function SheetExists(SName As String, Optional WBName As String) As Boolean Dim WS As Worksheet Dim WB As Workbook On Error Resume Next

If Len(WBName) > 0 Then 'check whether a workbook name was entered Set WB = Workbooks(WBName)

If WB Is Nothing Then Exit Function 'exit if the workbook isn't open


Set WB = ActiveWorkbook End If

Set WS = WB.Sheets(SName)

'if the sheet is found, WS will contain the sheet object

'if the sheet is not found, WS will contain Nothing

'so, if WS is NOT NOTHING, then it must be the sheet object and

'therefore returns TRUE

SheetExists = Not (WS Is Nothing) End Function

Here is an example of using this function:

Sub CheckForSheet()

Dim ShtExists As Boolean

ShtExists = SheetExists("Sheet9")

'notice that only one parameter was passed; the workbook name is optional If ShtExists Then

MsgBox "The worksheet exists!"


MsgBox "The worksheet does NOT exist!" End If End Sub

0 0

Post a comment