Check if sheet exists

[Noz2k]

Just want to add to my code a line to check whether the sheet exists and if not then exit sub


The sheet would be refererred to as

Sheet(P)

So something that essentially says

If Not Sheet(P) Exists Then
Exit Sub
Else
'Code
End If

 

[Sektor]

[COLOR="Blue"]Function[/COLOR] SheetExists(SheetName [COLOR="Blue"]As[/COLOR] String) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Boolean[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] sh [COLOR="Blue"]As[/COLOR] Worksheet
    [COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]Resume[/COLOR] [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]Set[/COLOR] sh = Worksheets(sh)
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] sh [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR] SheetExists = [COLOR="Blue"]True[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[VoG]

Assuming that P is a variable holding the sheet name try

If Not WorksheetExists(P) Then
    Exit Sub
Else
'Code
End If



Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
End Function

 

[jim may]

Sub Foo()
If Not SheetExists("P") Then
Exit Sub
Else
MsgBox "Sheet(P) Exists"
End If
End Sub


Function SheetExists(SheetName As String) As Boolean
Dim sh As Worksheet
On Error Resume Next
Set sh = Worksheets(SheetName)
If Not sh Is Nothing Then SheetExists = True
End Function

 

[Noz2k]

Works perfect thanks.

VoG, I'm not very experienced in creating my own functions yet, could you just explain how you code works a little bit please so I can learn.

 

[VoG]

WorksheetExists is False by default.

WorksheetExists = Worksheets(WSName).Name = WSName

If the sheet exists this will return True.

If the sheet doesn't exist that line of code will error (which is suppressed by On Error Resume Next) so result is False (the default).

 

[Noz2k]

Thought that was how it worked, with it being Boolean.

Thanks for the solution and explanation