Test on Sheet Names (English_XL97_SR2)

Topic

New Reply

Is it possible to test the existence of a sheet name? I have written the following code but would like a message box to appear if the sheet name selected does not exits.

Sub TestSheetName()
Dim PromptTab As String
PromptTab = Application.InputBox(“Enter Tab Name”)
Sheets(PromptTab).Select
End Sub

Reply | Quote

Replies

Try this:

Sub TestSheetName()
Dim PromptTab As String
Dim oSheet As Worksheet
    PromptTab = Application.InputBox("Enter Tab Name")
    On Error Resume Next
    Set oSheet = Worksheets(PromptTab)
    On Error GoTo 0
    If oSheet Is Nothing Then
        MsgBox "That sheet does not exist"
    Else
        oSheet.Select
    End If
End Sub

Reply | Quote

Thanks…it works.

Reply | Quote

I have a dislike for using “On Error” for checking the existance of something- though it is unavoidable at times. It’s possible that an error is something different from what I expect.

In this case, I’d prefer to do it this way:

Dim indx As Integer
Dim Found As Boolean
Dim PromptTab As String

  Found = False
  PromptTab = Application.InputBox("Enter Tab Name")
    
  For indx = 1 To Worksheets.Count
    If Worksheets(indx).Name = PromptTab Then
      Found = True
    End If
  Next
  
  If Not Found Then
    MsgBox "That worksheet does not exist"
  End If

Reply | Quote