Replace InputBox code with a UserForm & codes

[grady121]

With the help of this Forum I managed to build an Inputbox code that enables me to input golf scores for each player in turn. With a blank score entered if the player didn’t play. See code below.

If possible, I am now wanting to use a UseForm to do the same thing.

On the worksheet, each Player’s name is listed in Column B, (starting at B10) but with a variable number of players each year. In column S, I need to enter the score for each player in turn. i.e. S10

With the new UserForm, I plan to use Label1 to show the Players name, and Textbox1 to enable me to enter the score.
I can also use CommandButton1 as an ‘OK’ button to enter each score in turn.

My current code is: -

Sub AddScore()
 
    Dim ListRow, ListColumn, ScoreColumn, iRet As Integer
    Dim MyNewScore As String
    
    Range("S10").Select
    Application.CutCopyMode = False ' Clear Clipboard
    
    ' Using Names in Columns B (2), Add new Scores to Column S (19 across)
    Application.ScreenUpdating = True
    ListRow = 10: ListColumn = 2: ScoreColumn = 19
    
    While ActiveSheet.Cells(ListRow, ListColumn) <> "" ' Until names run out
        Do
            MyNewScore = 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, ScoreColumn))
            
            If MyNewScore = "" Then GoTo SCORE_OK
            
            ' Warn if score is over 40
            If MyNewScore > 40 Then iRet = MsgBox(ActiveSheet.Cells(ListRow, ListColumn) & " scored:- " _
                    & MyNewScore & ", is that correct ???" & vbNewLine & vbNewLine _
                    & "If not, just click 'No' to re-enter the score.", vbYesNo + vbQuestion, "Score Check")
            If iRet = vbYes Then GoTo SCORE_OK
        Loop Until (MyNewScore <= 40) Or (MyNewScore = "")
        
SCORE_OK:
        If MyNewScore <> "" Then
            ActiveSheet.Cells(ListRow, ScoreColumn) = MyNewScore  'If input is not empty, use the input
        Else: ActiveSheet.Cells(ListRow, ScoreColumn) = ""
        End If
        ListRow = ListRow + 1 ' Move to next Row
    Wend
    
End Sub

I’m also not sure where any of the new replacement codes will go. Are they on the Form or in a Module? - Any help appreciated.

 

[grady121]

Thanks once more Dave.
I consistently learn new things every time I come on this forum.

I did actually read the Helpfile before I re-posted and knew the value was 256, but did not realise I could add all the numeric values to get a total I could use in a code.
I really like the idea and will use this method in my future projects.

I also appreciate all your time and help you have given me.
Gray