CODEWITHSUNDEEP
vbaexcel
Cyril GUICHARD
Cyril GUICHARD

Reputation: 83

check if a hidden worksheet exists

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

Upvotes: 0

Views: 609

Answers (2)

Guest
Guest

Reputation: 430

'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

Upvotes: 0

QHarr
QHarr

Reputation: 84465

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

Upvotes: 1

Related Questions