VBA error select sheet

[jakobt]

Have a code to select a sheet. If the sheet dont exist I want a message box telling the sheet dont exist. Otherwise want to select the sheet.

Sub Open_TB3()
Dim entity As String




Entity = InputBox("Select entity")

if error Sheets(entity).Select

then messagebox (The entity does not exist)

Else: Sheets(entity).Select


End Sub

 

[gallen]

Maybe this?

Sub Open_TB3()
Dim entity As String
Dim ws As Worksheet


TryAgain:
    entity = InputBox("Select entity")
    
    On Error Resume Next
        Set ws = Worksheets(entity)
    On Error GoTo 0
    
    If ws Is Nothing Then
        If MsgBox("A sheet named: '" & entity & "' does not exist. Try again?", vbCritical + vbYesNo, "Error") = vbYes Then
            GoTo TryAgain
        End If
        Exit Sub
    End If
    
    'If we get here, sheet exists
    ws.Select
    
End Sub

 

[Norie]

Try this.

Sub Open_TB3()
Dim entity As String

    entity = InputBox("Select entity")

    If IsError(Evaluate("ISREF('" & entity & "'!A1)")) Then
        MsgBox "The entity does not exist"
    Else
        Sheets(entity).Select
    End If

End Sub

 

[jakobt]

Thanks it doesnt work complete. It always some up with the statement the entity does not exists. Even though the entity variable is equal to one of the sheet names:
Can you explain what this line does: Specifically what does the ISREF mean?
If IsError(Evaluate("ISREF('" & entity & "'!A1")) Then

 

[My Aswer Is This]

Try this:

Sub Select_Sheet()
'Modified  10/10/2018  7:23:25 AM  EDT
Dim ans As String
On Error GoTo M
ans = InputBox("Enter The sheet name you want", , ActiveSheet.Name)
Sheets(ans).Select
Exit Sub
M:
MsgBox "The sheet name you entered was  " & ans & vbNewLine & "That sheet does not exist"
End Sub

 

[Norie]

The code I posted will only work for sheets in the active workbook.

ISREF is an Excel worksheet function that returns TRUE if the argument passed to is is a valid range reference.