Code error when cancelling

**jiminic** · 09-03-2008, 08:53 PM

Hello everyone

I am trying to use the code below as a sheet selection mechanism.

Sub Sheet_Select()
myShts = ActiveWorkbook.Sheets.Count
For i = 1 To myShts
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next i
Dim mySht As Single
mySht = InputBox("Please enter the number of the transaction you would like to view." _
& vbCr & vbCr & myList, "Transaction Selection")

If mySht <> "" Then
            Sheets(mySht).Select
            
            Else
            Range("B1").Select
    End If

End Sub

If I enter a sheet number, the code works perfectly, but if I hit the cancel button I get a type mismatch error message that relates to this part of the code:

mySht = InputBox("Please enter the number of the transaction you would like to view." _
& vbCr & vbCr & myList, "Transaction Selection")

Does anyone have any suggestions on what is wrong with the code I am using?

Thank you

**mikerickson** · 09-03-2008, 09:39 PM

InputBox returns a string. And you had mySht dimensioned as Single.
This changes that and adds a bit more protection incase the user enters a non-numeric value in the InputBox.

Sub Sheet_Select()
Dim myShts As Long, myList As String
Dim i As Long

myShts = ActiveWorkbook.Sheets.Count
For i = 1 To myShts
    myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next i
Dim mySht As String
mySht = InputBox("Please enter the number of the transaction you would like to view." _
& vbCr & vbCr & myList, "Transaction Selection")

On Error Resume Next
If Not (IsNumeric(Sheets(Val(mySht)).Index)) Then
    Range("b1").Select
Else
    Sheets(Val(mySht)).Activate
End If
On Error GoTo 0
End Sub