Excel VBA - Does Sheet Exist

I can do

Code:

---

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

Code:

---

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

---

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

Hi Hack, :wave:

I have been using the following.

VB Code:

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.

I'll give it a shot. Thanks Torc! :thumb:

Hi Hack

Another way to do it is...

vb Code:

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

Quote:

---

Originally Posted by Torc

' 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:

Code:

---

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

---

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

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