Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Test on Sheet Names (English_XL97_SR2)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test on Sheet Names (English_XL97_SR2)

    Try this:

    <pre>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
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test on Sheet Names (English_XL97_SR2)

    Thanks...it works.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test on Sheet Names (English_XL97_SR2)

    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:<pre>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</pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •