Goto specific sheet by entering it's name via VBA

[omairhe]

Hello,

I have a workbook that consists of 12 sheets for 12 months (eg. January, February, March, etc)

I want a text box asking the end-user to enter a specific month's name and thus taking the user to that sheet while making that sheet active.

But that's not all, some sheets with the month's name are also hidden. Hence, if the above scenario is met it would also need to unhide the sheet entered and then make it as active.

For instance a user enters January in text box, but it was already unhide so the code takes the user to january sheet.
but now the user enters February in text box, it was hidden so , it first unhides it and then takes the user to january sheet.

Note: There is no operation for hiding the sheet again, but only unhide is required.

Will appreciate any help.

Thank you.

 

[Joe4]

Try this:

Sub MyUnhideMacro()

    Dim shName As String
    
    On Error GoTo err_chk
    
'   Prompt to enter sheet name
    shName = InputBox("Enter sheet name you want to go to")
    
'   Unhide sheet if hidden
    Sheets(shName).Visible = True

'   Activate sheet
    Sheets(shName).Select

    Exit Sub
    
err_chk:
    If Err.Number = 9 Then
        MsgBox "No sheet found with name " & shName, vbOKOnly, "ERROR!!!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

 

[B___P]

Hi omairhe,
give this code a go

Sub MyWorksheet()

    Dim shID As String
    msg = "Enter sheet ID you want to go to" & vbCrLf & vbCrLf
    msg = msg & "ID worksheet_name" & vbCrLf
    For Each ws In ActiveWorkbook.Worksheets
        Select Case ws.Name
            Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
                msg = msg & ws.Index & " " & ws.Name & vbCrLf
        End Select
    Next ws
    shID = InputBox(msg)
    If Len(shID) = 0 Then End
    Sheets(CLng(shID)).Visible = True
    Sheets(CLng(shID)).Select
End Sub

Hope this helps

 

[omairhe]

Thank you ! ! !

 

[mumps]

Another approach:

Sub MyUnhideMacro()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Nothing
    On Error Resume Next
    Set ws = Worksheets(InputBox("Enter sheet name."))
    On Error GoTo 0
    If Not ws Is Nothing Then
        ws.Visible = True
        ws.Activate
    Else
        MsgBox ("The sheet does not exist.")
    End If
    Application.ScreenUpdating = True
End Sub