Find Sheet

[gerald24]

Hi Guys,

Could you please check what's missing? I want to find sheet even though part of its name only is being searched.

Sub FindWS()
    Dim strWSName As String
    
    strWSName = InputBox("Enter the sheet name to serach for")
    If strWSName = vbNullString Then
        Exit Sub
    End If
    
    If SheetExists(strWSName) Then
        Worksheets(strWSName).Activate
    Else
        'look if it at least contains part of the name
        Dim s As Worksheet
        For Each s In ThisWorkbook.Sheets
            If InStr(s.Name, strWSName) > 0 Then
                s.Activate
                Exit Sub
            End If
        Next s
        MsgBox "That sheet name does not exist!"
    End If
    
End Sub

Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True
End Function

Thaaaaaaanks

 

[Momentman]

Does it not work?

 

[gerald24]

Yes. it does work.

 

[gerald24]

Sorry. I meant it does not work

Yes. it does work.

 

[gerald24]

up.

 

[Norie]

How does it not work?

 

[gerald24]

I tried in an new workbook. so there's a sheet1, sheet2, sheet3. Supposedly, when I try to search sheet, it should bring me to sheet 1 right? but it only returns the "That sheet name does not exist!" because it returns false.

 

[Momentman]

Except you are typing the Sheet name as it actually appears "Sheet 1" and "Sheet1" are different

 

[gerald24]

Except you are typing the Sheet name as it actually appears "Sheet 1" and "Sheet1" are different

but what I want to accomplish is to search a part of the sheet name and it will lead me to the sheets that contains it.

Example, I want to search for "Apples" sheet, but I only searched for "App" but it will still lead me to that sheet or any other sheets that contains "App"

 

[Momentman]

Case sensitivity!