Worksheet Existance....

Posted by Christopher Patrick on September 06, 2001 7:36 AM

Hi all-

Is there a way in Visual Basic to see if a worksheet exists? I have a delete macro, that deletes a sheet based on the user's input of the sheet name, but I want to verify the sheet exists before deletion.

Thanks for any help.

Chris

Posted by Dax on September 06, 2001 10:24 AM

There is indeed. How about this?

Use this function:-

Function DoesSheetExist(sSheetName As String) As Boolean
Dim sht As Object
For Each sht In ThisWorkbook.Sheets
If sht.Name = sSheetName Then DoesSheetExist = True: Exit Function
Next
End Function

An example of how to use this:-

Sub TestOfSheetExists()
Dim sSheetToDelete As String
Dim sht As Object

sSheetToDelete = InputBox("Enter sheet name", "Sheet to delete")
If sSheetToDelete = "" Then Exit Sub
If DoesSheetExist(sSheetToDelete) = True Then
Application.DisplayAlerts = False
Sheets(sSheetToDelete).Delete
Application.DisplayAlerts = True
Else
MsgBox "The specified sheet name doesn't exist.", vbInformation, "Error!"
End If
End Sub

HTH,
Dax

Posted by Christopher Patrick on September 07, 2001 10:40 AM

Thank you!

That is perfect..exactly what I was looking for. Thank you so much for the information.

Chris