check if a hidden worksheet exists

Question

I read alot of topics about how to look for an existing worksheet because I need it for a workbook.

I implemented following code to do so after all my searches :

'check if woksheet "tool" exists
On Error Resume Next
If Not Len(Worksheets("tool").Name) Then
    ThisWorkbook.Worksheets.Add(after:=Worksheets("Data"))
    ActiveSheet.Name = "tool"
Else
    Worksheets("tool").Activate
End If
On Error GoTo 0

The code runs well as long as the "tool" worksheet is visible and not anymore when hidden, as I need it to be.

I haven't been able to find satisfactory answer so far

score 1 · Accepted Answer · answered Jul 05 '18 at 09:48

Something like the following, using a techique by @Rory to test if sheet exists:

Option Explicit
Public Sub test()
    If Not Evaluate("ISREF('" & "tool" & "'!A1)") Then
        ThisWorkbook.Worksheets.Add after:=Worksheets("Data")
        ActiveSheet.Name = "tool"
    Else
        'Worksheets("tool").Visible = True '<== Also make visible? Optional
        Worksheets("tool").Activate
    End If
End Sub

score 0 · Answer 2 · answered Jul 06 '18 at 01:49

'To check if a worksheet exists
Public Function worksheetExists(ByVal wb As Workbook, ByVal sheetNameStr As String) As Boolean

On Error Resume Next
worksheetExists = (wb.Worksheets(sheetNameStr).Name <> "")
Err.Clear: On Error GoTo 0

End Function

check if a hidden worksheet exists

2 Answers2