Does Sheet Name Exist in a Workbook

  • Thread starter Thread starter WhytheQ
  • Start date Start date
W

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
 
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
 
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
 
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 -
Click to expand...


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

Kind Regards
Jason.
 
Back
Top