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

You could do this.

It continues to ask for the row number until it is <=2345.

Cancel will escape the loop.

No error message is displayed.

Private Sub GoToRow_Click()
Dim lnRow As Variant

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

 

[ipbr21054]

The row value will increase each day, 2345 was an example of what it currently is.

Also just clicking on OK pops up error message but doesnty say what it is.

I will just use one of the previous advised from above thanks

 

[HighAndWilder]

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

Or this where a message appears and the last row is hard coded. Not very flexible.

Private Sub GoToRow_Click()
Dim lnRow As Variant

  ' Turn off errors.
  On Error Resume Next
  
  Do
  
    lnRow = Application.InputBox("Enter number", Type:=1) ' The type is number
      
    ' Turn on errors.
    On Error GoTo 0
      
    If lnRow = False Then
      Exit Sub
    End If
    
    If lnRow > 2345 Then
      MsgBox "The number that you enter should be less than or equal to 2345.", vbOKOnly, "Warning!"
    End If
  
  Loop Until lnRow <= 2345
  
  Cells(lnRow, 1).Select
    
End Sub

 

[HighAndWilder]

The row value will increase each day, 2345 was an example of what it currently is.

Also just clicking on OK pops up error message but doesnty say what it is.

I will just use one of the previous advised from above thanks

Or this where it does not let entry more than the last row on the active sheet.

Private Sub GoToRow_Click()
Dim lnRow As Variant
Dim lngLastRow As Long

  lngLastRow = ActiveSheet.UsedRange.Rows.Count
  
  ' Turn off errors.
  On Error Resume Next
  
  Do
  
    lnRow = Application.InputBox("Enter number", Type:=1) ' The type is number.
      
    ' Turn on errors.
    On Error GoTo 0
      
    If lnRow = False Then
      Exit Sub
    End If
    
    If lnRow > lngLastRow Then
      MsgBox "The number that you enter should be less than or equal to " & lngLastRow & ".", vbOKOnly, "Warning!"
    End If
  
  Loop Until lnRow <= lngLastRow
  
  Cells(lnRow, 1).Select
    
End Sub

 

[ipbr21054]

I will leave it thanks.
Im just seeing more & more odd messages

 

[dmt32]

I will leave it thanks.
Im just seeing more & more odd messages

If you want to stay with your original InputBox.Function approach should be able to avoid need for additional prompts

see if this update is of any help to you.

Private Sub GoToRow_Click()
    Dim lnRow       As Variant
    Dim LastRow     As Long
    Dim strDefault As String
   
    Const ErrPrompt As String = Const ErrPrompt As String = "The Number Must Be > 0 And Less Or Equal To "
   
    LastRow = ActiveSheet.UsedRange.Rows.Count

    Do
        lnRow = InputBox("ENTER ROW NUMBER.", "GO To ROW NUMBER", strDefault)
        'cancel pressed
        If StrPtr(lnRow) = 0 Then Exit Sub
        lnRow = Val(lnRow)
        strDefault = ErrPrompt & LastRow
    Loop Until lnRow > 0 And lnRow <= LastRow
   
    Cells(CLng(lnRow), 1).Select
End Sub

Dave

 

[HighAndWilder]

If you want to stay with your original InputBox.Function approach should be able to avoid need for additional prompts

see if this update is of any help to you.

Private Sub GoToRow_Click()
    Dim lnRow       As Variant
    Dim LastRow     As Long
    Dim strDefault As String
  
    Const ErrPrompt As String = Const ErrPrompt As String = "The Number Must Be > 0 And Less Or Equal To "
  
    LastRow = ActiveSheet.UsedRange.Rows.Count

    Do
        lnRow = InputBox("ENTER ROW NUMBER.", "GO To ROW NUMBER", strDefault)
        'cancel pressed
        If StrPtr(lnRow) = 0 Then Exit Sub
        lnRow = Val(lnRow)
        strDefault = ErrPrompt & LastRow
    Loop Until lnRow > 0 And lnRow <= LastRow
  
    Cells(CLng(lnRow), 1).Select
End Sub

Dave

So many ways to do the same job.

Line of code should read:

Const ErrPrompt As String = "The Number Must Be > 0 And Less Or Equal To "

 

[dmt32]

So many ways to do the same job.

There is

Line of code should read:

Const ErrPrompt As String = "The Number Must Be > 0 And Less Or Equal To "

thanks I know - hit the 10 min rule to edit double paste but hopefully OP if wanted to use, could resolve themself.

Dave