Do not show type mismatch message when clicking cancel on Msgbox

[ipbr21054]

I have a code shown below where the user enters a row number & is then taken to it.
Somethimes the user will click on Cancel ot the red cross to exit BUT the Type mismatch message is shown.

What do i need to add to the code so in future if the message is not shwon.
Thanks

Private Sub GoToRow_Click()
   Dim lnRow As Long
    lnRow = InputBox("ENTER ROW NUMBER.", "GO TO ROW NUMBER")
    Cells(lnRow, 1).Select
End Sub

 

[HighAndWilder]

I have a code shown below where the user enters a row number & is then taken to it.
Somethimes the user will click on Cancel ot the red cross to exit BUT the Type mismatch message is shown.

What do i need to add to the code so in future if the message is not shwon.
Thanks

Private Sub GoToRow_Click()
   Dim lnRow As Long
    lnRow = InputBox("ENTER ROW NUMBER.", "GO TO ROW NUMBER")
    Cells(lnRow, 1).Select
End Sub

A change to the variable type to Variant instead of Long will sort it.

Private Sub GoToRow_Click()
Dim lnRow As Variant

    lnRow = InputBox("ENTER ROW NUMBER.", "GO TO ROW NUMBER")
    
    If lnRow <> "" Then
      Cells(lnRow, 1).Select
    End If
    
End Sub

 

[ipbr21054]

It worked, many thanks

 

[PeteWright]

I would also check for anything else then a number like letters:

Private Sub GoToRow_Click()
   Dim lnRow As Variant
    lnRow = InputBox("ENTER ROW NUMBER.", "GO TO ROW NUMBER")
    If (lnRow = 0 Or lnRow = vbNullString) Then Exit Sub
    If (VarType(lnRow) <> vbInteger Or VarType(lnRow) <> vbLong) Then Exit Sub
    Cells(lnRow, 1).Select
End Sub

 

[ipbr21054]

That doesnt take me to a row after i click oK or hit enter

 

[PeteWright]

oops, my fault. Try this:

Private Sub GoToRow_Click()
   Dim lnRow As Variant
    lnRow = InputBox("ENTER ROW NUMBER.", "GO TO ROW NUMBER")
    If (lnRow = 0 Or lnRow = vbNullString) Then Exit Sub
    If IsNumeric(lnRow) Then
      lnRow = CLng(lnRow)
    Else
      Exit Sub
    End If
    Cells(lnRow, 1).Select
End Sub

 

[dmt32]

That doesnt take me to a row after i click oK or hit enter

try following & see if resolves

Private Sub GoToRow_Click()
    Dim lnRow       As Variant
    Do
        lnRow = InputBox("ENTER ROW NUMBER.", "GO To ROW NUMBER")
        'cancel pressed
        If StrPtr(lnRow) = 0 Then Exit Sub
        lnRow = Val(lnRow)
    Loop Until lnRow > 0
 
    Cells(CLng(lnRow), 1).Select

End Sub

Solution should ignore any non numeric value entered

Dave

 

[ipbr21054]

That worked thanks
Post #6

 

[HighAndWilder]

That worked thanks
Post #6

You could also use the Application.InputBox which displays a message if the entry is incorrect.

Just see which one you like the best.

Private Sub GoToRow_Click()
Dim lnRow As Variant

  ' Turn off errors.
  On Error Resume Next
    
  lnRow = Application.InputBox("Enter number", Type:=1) ' The type 1 is a number.
    
  ' Turn on errors.
  On Error GoTo 0
    
  If lnRow <> False Then
    Cells(lnRow, 1).Select
  End If
  
End Sub

 

[ipbr21054]

I will do that.
So if you want to suggest one more then i will also look at that.

Lets say my last row with values is 2345

If a user enters a row number greater than that also show the Msgbox.
After all we should only be taken to a row with values in.

Thanks