Limit an InputBox to just 2 numbers

[grady121]

On my Golf Handicap workbook and using the golfer’s names listed in column B, I use an InputBox as a way to enter each players scores in turn, onto the current worksheet in 2 different columns. (L & AM)

I need to limit the score I can enter in the InputBox to just 2 numbers (i.e. 99 or less). Can anyone suggest any additions to my part-code that will meet my needs?

 Sub AddScore()
 
    Dim ListRow As Integer, ListColumn As Integer, NewDataColumn As Integer, NewDataColumn2 As Integer
    Dim MyNewData As String
    Dim iRet As Integer

    
    ' Using Names in Columns B, Add new Scores to Columns L & AM
    Application.ScreenUpdating = True
    ListRow = 10: ListColumn = 2: NewDataColumn = 12: NewDataColumn2 = 39 '(Columns B, L & AM)
    While ActiveSheet.Cells(ListRow, ListColumn) <> ""
        MyNewData = InputBox("Enter the Scores for:- " & vbNewLine & vbNewLine _
        & ActiveSheet.Cells(ListRow, ListColumn) & vbNewLine & vbNewLine & vbNewLine _
        & "Click 'OK' or press 'Enter' to add next players score." & vbNewLine & vbNewLine _
        & "(If a Player did not play - click 'OK' or press 'Enter')", _
        "Add Scores", ActiveSheet.Cells(ListRow, NewDataColumn))    'Get input
        If MyNewData <> "" Then
            ActiveSheet.Cells(ListRow, NewDataColumn) = MyNewData  'If input is not empty, use the input
            ActiveSheet.Cells(ListRow, NewDataColumn2) = MyNewData
        Else: ActiveSheet.Cells(ListRow, NewDataColumn) = ""
        End If
        ListRow = ListRow + 1
    Wend
    
End Sub

Any help appreciated.

 

[Tetra201]

Maybe

...
Do
    MyNewData = InputBox("Enter the Scores for:- " & vbNewLine & vbNewLine _
    & ActiveSheet.Cells(ListRow, ListColumn) & vbNewLine & vbNewLine & vbNewLine _
    & "Click 'OK' or press 'Enter' to add next players score." & vbNewLine & vbNewLine _
    & "(If a Player did not play - click 'OK' or press 'Enter')", _
    "Add Scores", ActiveSheet.Cells(ListRow, NewDataColumn))    'Get input
Loop Until (MyNewData Like "##") Or (MyNewData = "")
...

 

[grady121]

Thanks for your quick response Tetra201.
I've modified my code as below and it works well, but I can't get it to move on to the next player, if I enter just a single number.
Some of our players are good at ruining a good walk !! LOL
Code:

    While ActiveSheet.Cells(ListRow, ListColumn) <> ""
    Do
    MyNewData = InputBox("Enter the Scores for:- " & vbNewLine & vbNewLine _
    & ActiveSheet.Cells(ListRow, ListColumn) & vbNewLine & vbNewLine & vbNewLine _
    & "Click 'OK' or press 'Enter' to add next players score." & vbNewLine & vbNewLine _
    & "(If a Player did not play - click 'OK' or press 'Enter')", _
    "Add Scores", ActiveSheet.Cells(ListRow, NewDataColumn))    'Get input
    If Len(MyNewData) > 2 Then MsgBox "The score for:- '" & ActiveSheet.Cells(ListRow, ListColumn) & "' is too high." & vbCrLf & vbCrLf _
             & "Max of 2 Numbers ! ... Please re-enter score.", vbOKOnly + vbCritical, "Score Error"
    Loop Until (MyNewData = "#") Or (MyNewData Like "##") Or (MyNewData = "")
    
    If MyNewData <> "" Then
            ActiveSheet.Cells(ListRow, NewDataColumn) = MyNewData  'If input is not empty, use the input
            ActiveSheet.Cells(ListRow, NewDataColumn2) = MyNewData
        Else: ActiveSheet.Cells(ListRow, NewDataColumn) = ""
        End If
        ListRow = ListRow + 1
    Wend

I've tried adding (MyNewData = "#"), but do you have any ideas.

 

[L. Howard]

Maybe like this.

Howard

Option Explicit
 Sub AddScore_Two()
 
    Dim ListRow As Integer, ListColumn As Integer, NewDataColumn As Integer, NewDataColumn2 As Integer
    Dim MyNewData As String
    Dim iRet As Integer
    
    ' Using Names in Columns B, Add new Scores to Columns L & AM
  '  Application.ScreenUpdating = True
    ListRow = 10: ListColumn = 2: NewDataColumn = 12: NewDataColumn2 = 39 '(Columns B, L & AM)
    While ActiveSheet.Cells(ListRow, ListColumn) <> ""
nTwo:
        MyNewData = InputBox("Enter the Scores for:- " & vbNewLine & vbNewLine _
        & ActiveSheet.Cells(ListRow, ListColumn) & vbNewLine & vbNewLine & vbNewLine _
        & "Click 'OK' or press 'Enter' to add next players score." & vbNewLine & vbNewLine _
        & "(If a Player did not play - click 'OK' or press 'Enter')", _
        "Add Scores", ActiveSheet.Cells(ListRow, NewDataColumn))    'Get input
        
       If Len(MyNewData) > 2 Then
            MsgBox "Invalid entry"
            GoTo nTwo
          Else
           MsgBox "You entered:" & MyNewData
        
        If MyNewData <> "" Then
            ActiveSheet.Cells(ListRow, NewDataColumn) = MyNewData  'If input is not empty, use the input
            ActiveSheet.Cells(ListRow, NewDataColumn2) = MyNewData
          Else: ActiveSheet.Cells(ListRow, NewDataColumn) = ""
        End If
        
       End If
        ListRow = ListRow + 1
    Wend
    
End Sub

 

[Tetra201]

Thanks for your quick response Tetra201.
I've modified my code as below and it works well, but I can't get it to move on to the next player, if I enter just a single number.
Some of our players are good at ruining a good walk !! LOL
Code:

    While ActiveSheet.Cells(ListRow, ListColumn) <> ""
    Do
    MyNewData = InputBox("Enter the Scores for:- " & vbNewLine & vbNewLine _
    & ActiveSheet.Cells(ListRow, ListColumn) & vbNewLine & vbNewLine & vbNewLine _
    & "Click 'OK' or press 'Enter' to add next players score." & vbNewLine & vbNewLine _
    & "(If a Player did not play - click 'OK' or press 'Enter')", _
    "Add Scores", ActiveSheet.Cells(ListRow, NewDataColumn))    'Get input
    If Len(MyNewData) > 2 Then MsgBox "The score for:- '" & ActiveSheet.Cells(ListRow, ListColumn) & "' is too high." & vbCrLf & vbCrLf _
             & "Max of 2 Numbers ! ... Please re-enter score.", vbOKOnly + vbCritical, "Score Error"
    Loop Until (MyNewData = "#") Or (MyNewData Like "##") Or (MyNewData = "")
    
    If MyNewData <> "" Then
            ActiveSheet.Cells(ListRow, NewDataColumn) = MyNewData  'If input is not empty, use the input
            ActiveSheet.Cells(ListRow, NewDataColumn2) = MyNewData
        Else: ActiveSheet.Cells(ListRow, NewDataColumn) = ""
        End If
        ListRow = ListRow + 1
    Wend

I've tried adding (MyNewData = "#"), but do you have any ideas.

Good catch!

Try:

Loop Until (MyNewData [COLOR=#ff0000]Like[/COLOR] "#") Or (MyNewData Like "##") Or (MyNewData = "")

 

[grady121]

Thanks Tetra201, that will do nicely...
Works a dream.

 

[Tetra201]

You are welcome.