Option Explicit
Sub Test_xlSheetExists()
Dim xlRtn As Integer
Dim xlBookName As String, xlSheetName As String, MsgTitle As String
MsgTitle = "Demo of xlSheetExists"
xlBookName = InputBox("enter name of workbook to be tested." & vbCrLf & _
"[leave empty for active workbook]" & vbCrLf & _
"[name is not case sensitive]", MsgTitle)
xlSheetName = InputBox("enter name of sheet to be tested." & vbCrLf & _
"[name is not case sensitive]", MsgTitle)
If xlSheetName = "" Then Exit Sub
xlRtn = xlSheetExists(xlSheetName, xlBookName)
If xlBookName = "" Then xlBookName = ActiveWorkbook.Name
Select Case xlRtn
Case 0
MsgBox "chart or sheet name entered = " & xlSheetName & vbCrLf & _
"xlBookName entered (or implied) = " & xlBookName & vbCrLf & _
xlSheetName & " does not exist", _
vbInformation & vbOKOnly, MsgTitle
Case 1
MsgBox "chart or sheet name entered = " & xlSheetName & vbCrLf & _
"xlBookName entered (or implied) = " & xlBookName & vbCrLf & _
xlSheetName & " is a worksheet", _
vbInformation & vbOKOnly, MsgTitle
Case 2
MsgBox "chart or sheet name entered = " & xlSheetName & vbCrLf & _
"xlBookName entered (or implied) = " & xlBookName & vbCrLf & _
xlSheetName & " is a chartsheet", _
vbInformation & vbOKOnly, MsgTitle
End Select
End Sub
Function xlSheetExists(SheetName As String, Optional WorkBookName As String) As Integer
Dim xlobj As Object
If WorkBookName = vbNullString Then WorkBookName = ActiveWorkbook.Name
On Error Resume Next
Set xlobj = Workbooks(WorkBookName).Worksheets(SheetName)
If Err = 0 Then
xlSheetExists = 1
Exit Function
End If
On Error Resume Next
Set xlobj = Workbooks(WorkBookName).Charts(SheetName)
If Err = 0 Then
xlSheetExists = 2
Exit Function
End If
xlSheetExists = 0
End Function
|