How to check from VBA if sheet exists?

[Alen]

How to check from VBA if sheet named "JohnDoe" exists in workbook?

Sub del_sheet()

if exist(sheets("JohnDoe")) then
application.displayalerts = false
sheets("JohnDoe").delete
application.displayalerts = true
end if

With what code I have to put instead of "exist(sheets("JohnDoe"))"

Regards, Alen

 

[Thierry Paradis]

Here's the code:

Public Sub VérifierFeuille(ByVal strValeur As String)
Dim wSheet As Worksheet

On Error Resume Next
Set wSheet = ActiveWorkbook.Sheets(strValeur)
If wSheet Is Nothing Then
MsgBox "The sheet" + strValeur + " is not present, the application
will be stop.", vbCritical + vbOKOnly, "Error"
End
Else
Set wSheet = Nothing
End If
End Sub

 

[Dave Peterson]

Chip Pearson posted this function:

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

'and you can use it like:
....
if worksheetexists("myname",activeworkbook) then
application.displayalerts = false
worksheets("Myname").delete
application.displayalerts = true
end if

============
But if I'm deleting, I don't care:

application.displayalerts = false
on error resume next
sheets("JohnDoe").delete
on error goto 0
application.displayalerts = true

If it's not there, just ignore the error.