Does Sheet Name Exist in a Workbook

[WhytheQ]

Hello All,

Pretty trivial puzzle for someone I'm sure.

Without looping though all the sheets in a workbook is it possible to
find out (TRUE or FALSE) if a sheet named "mySheet" exists within the
activeworkbook ?

I tried setting something up using On Error but still got a run-time
error 9!

Any help much appreciated

Jason

 

[Patrick Molloy]

two useful functions

Option Explicit

Sub test1()
MsgBox SheetExists("Sheet1")
End Sub
Sub test2()
Dim text As String
Dim ws As Worksheet
text = "sheet1"
Set ws = GetSheet(text)
MsgBox text & " exists: " & Not (ws Is Nothing)

End Sub


Public Function SheetExists(sName As String) As Boolean
On Error Resume Next
Dim ws As Worksheet
Set ws = Worksheets(sName)
SheetExists = Not (ws Is Nothing)
On Error GoTo 0
End Function
Public Function GetSheet(sName As String) As Worksheet
On Error Resume Next
Dim ws As Worksheet
Set ws = Worksheets(sName)
Set GetSheet = ws

 

[john]

If you refer to a sheet that does not exist you get an error so at simple
level, just test for that error.

Something like following:

Sub CheckSheet()
Dim WS As Worksheet

On Error Resume Next

Set WS = Worksheets("Sheet3")

If Err > 0 Then

MsgBox " Sheet Does Not Exist"

Else

MsgBox "Sheet Exists"

End If

End Sub

 

[WhytheQ]

two useful functions

Option Explicit

Sub test1()
MsgBox SheetExists("Sheet1")
End Sub
Sub test2()
Dim text As String
Dim ws As Worksheet
text = "sheet1"
Set ws = GetSheet(text)
MsgBox text & " exists: " & Not (ws Is Nothing)

End Sub

Public Function SheetExists(sName As String) As Boolean
On Error Resume Next
Dim ws As Worksheet
Set ws = Worksheets(sName)
SheetExists = Not (ws Is Nothing)
On Error GoTo 0
End Function
Public Function GetSheet(sName As String) As Worksheet
On Error Resume Next
Dim ws As Worksheet
Set ws = Worksheets(sName)
Set GetSheet = ws







- Show quoted text -

Nice one Patrick - will add those little beauties to my small
collection of functions

Kind Regards
Jason.