Excel VBA - Does Sheet Exist

[Hack]

I can do

If Blah = True Then
     Sheets("My Sheet Name").Visible = True
End If

And that works fine, but I want to alter this to check whether or not the sheet even exists. I don't know what it does like, but I can tell you it does not like this

If Sheets("My Sheet Name").Name = True Then

So, how to I determine whether or not a sheet exists in my workbook?

[Torc]

Hi Hack,

I have been using the following.

Function WorksheetExists(TWbk As String, WName As String) As Boolean
' Returns True if a worksheet WName exists in Workbook TWbk
Dim Counter As Long
    WorksheetExists = False
    Counter = 1
    Do
        ' The Worksheet names are Case Insensitive, hence the
        ' Upper Case conversion in the comparison.
        If UCase(WName) = UCase(Workbooks(TWbk).Worksheets(Counter).Name) Then
            WorksheetExists = True
            Exit Do
        End If
        If Counter = Workbooks(TWbk).Worksheets.Count Then
            Exit Do
        End If
        Counter = Counter + 1
    Loop
End Function

Someone else might be able to come up with a more elegant solution but I know this one works.

[Hack]

I'll give it a shot. Thanks Torc!

[koolsid]

Hi Hack

Another way to do it is...

Private Sub CommandButton1_Click()
 
Dim WSName As String, Found As Boolean
Found = False
 
'Replace Sheet3 with the name of the relevant sheet
WSName = "Sheet3"
 
'loop thru the names of the sheets
For i = 1 To ActiveWorkbook.Sheets.Count
 
    If UCase(WSName) = UCase(ActiveWorkbook.Sheets(i).Name) Then
        Found = True
        Exit For
    Else
        Found = False
    End If
 
Next i
 
'Display Status
If Found = False Then
    MsgBox "Sheet Doesn't exist"
Else
    MsgBox "Sheet exists"
End If
 
End Sub

[anhn]

' The Worksheet names are Case Insensitive, hence the
' Upper Case conversion in the comparison.

Yes, worksheet names are Case "Insensitive" (ie. Not Case Sensitive), so do not need to use Upper Case conversion to compare, unless the Option Compare Binary are used.

@Hack, This is a much simpler way to check whether a worksheet name exist without a loop:

Dim ws As Worksheet

On Error Resume Next
Set ws = ThisWorkbook.Worksheets("YourSheetName")
If Err > 0 then
   MsgBox "Worsheet 'YourSheetName' Does Not Exist!"
   Err.Clear
Else
   MsgBox "Worsheet 'YourSheetName' Found!"
End If
On Error Goto

[Hack]

Thank you koolsid and anhn.....I will give these a try as well.

[JB123]

Hack, here is another option...

Option Explicit
Option Base 0
Option Compare Binary
DefLng A-Z
Function DoesSheetExist()

Dim SheetName As String, DoesWorkSheetExist As Boolean

SheetName = "My Sheet Name"

DoesWorkSheetExist = CycleThroughSheets(SheetName)

If DoesWorkSheetExist = True Then MsgBox """" & SheetName & _
""" worksheet does exist.", vbOKOnly, "Sheet Exists!" Else _
MsgBox """" & SheetName & """ worksheet does not exist.", vbOKOnly, _
"Sheet Does Not Exists!"

End Function

Function CycleThroughSheets(ByRef SheetName As String) As Boolean

Dim Wks As Excel.Worksheet

For Each Wks In Worksheets

If Wks.Name = SheetName Then: _
CycleThroughSheets = True: Exit Function

Next Wks
End Function