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.

 

[dmt32]

Hi,
You can create a userform to do what you want but Just quickly looking at your code, InputBox code seems to be doing what you want to try & emulate with a userform & just really wondering why want to change it?

Also, just a comment about your declarations
This line:

Dim ListRow, ListColumn, ScoreColumn, iRet As Integer

The first three variables are VARIANTS only the last variable is of data type Integer you have specified.
Even if declarations are on the same line, you must explicitly declare each variable with the required data type otherwise, they are variants.

Dim ListRow As Long, ListColumn As Long, ScoreColumn As Long, iRet As Integer

Worth noting that msgbox has its own enumerated VBMsgboxResult
If you use this you will get the intelliense showing the constants available.

Dim Iret As VbMsgBoxResult

Hope Helpful

Dave

 

[grady121]

Thanks for your reply & help Dave.
I must confess I still know very little about variables and tend to just follow other posts that seem to work.
I've amended my code accordingly.

The reason I considered the use of a new UserForm is, I've found it difficult to position the InputBox exactly where I want it on the screen.
A UserForm is a lot easier to achieve the result I was looking for.

Thanks again

 

[dmt32]

Hi,
InputBox Function has xpos and ypos parameters that you can specify where it appears on screen which may achieve what you want.

You can read more in VBA Helpfile: https://docs.microsoft.com/en-us/of...tFrameworkMoniker-Office.Version=v15)&rd=true

Dave

 

[grady121]

Many thanks Dave.
The link really helped with the examples, I should have found and read it myself !

 

[dmt32]

Many thanks Dave.
The link really helped with the examples, I should have found and read it myself !

Hi,
No worries, many posting here overlook searching the helpfile where many of the answers can be found

Going a little off piste I had further look at your code & should be able to avoid using two loops.
You can read all the data into an array & take the values from it in the Inpubox Do Loop

something like this:

Sub AddScore()
    
    Dim i As Integer
    Dim ListRow As Long, LastRow As Long
    Dim Iret As VbMsgBoxResult
    Dim MyNewScore As Variant, PlayersName As Variant
    Dim Prompt As String, Default As String
    
'columns holding players Names & their score
    Const ListColumn As Integer = 2, ScoreColumn As Integer = 19
'start row
    ListRow = 10
'get data row count
    LastRow = Cells(Rows.Count, ListColumn).End(xlUp).Row
    LastRow = LastRow - ListRow + 1
    
'array Using Names in Columns B (2), and Scores from Column S (18 across)
    PlayersName = Cells(ListRow, ListColumn).Resize(LastRow, 18).Value
    
'index counter for array
    i = 1
        Do
'apply values to variables
            Prompt = GetPrompt(PlayersName(i, 1), 1, 0)
            Default = PlayersName(i, 18)
            Iret = vbYes
'get user input
            MyNewScore = InputBox(Prompt, "Add Score", Default)
'cancel pressed
            If StrPtr(MyNewScore) = 0 Then Exit Sub
'Warn if score is over 40
            If Val(MyNewScore) > 40 Then Iret = MsgBox(GetPrompt(PlayersName(i, 1), 2, MyNewScore), 36, "Score Check")
'if vbYes add score
            If Iret = vbYes Then Cells(ListRow, ScoreColumn) = Val(MyNewScore): i = i + 1: ListRow = ListRow + 1
        Loop Until i > UBound(PlayersName, 1)
End Sub


Function GetPrompt(ParamArray Entry() As Variant) As String
    GetPrompt = Choose(Entry(1), "Enter the Scores for:- " & vbNewLine & vbNewLine _
    & Entry(0) & 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')", _
    Entry(0) & " scored:- " & Entry(2) & ", is that correct ???" & vbNewLine & vbNewLine _
    & "If not, just click 'No' to re-enter the score.")
End Function

Using an array means you do not have to access the cell ranges to read data with each iteration of your loop which although in your case, probably won’t make great deal of difference, is a faster way to work with data.

Note that I also have added a function (additional code) which returns the prompts for both the inputbox & msgbox in an attempt to make code cleaner & easier to read.

You will need to include the xpos & ypos parameters to the InputBox call as required.

Hope Helpful

Dave

 

[grady121]

Many thanks for the revised code.
It's surprising the number of different ways you can 'crack a nut'...

Just one question.
Is there a simple way to replace the '0' (zero score if the player didn't turn up) as your new code now produces, with either a blank (""), or preferably "'--" ?

Thanks again

 

[dmt32]

Is there a simple way to replace the '0' (zero score if the player didn't turn up) as your new code now produces, with either a blank (""), or preferably "'--" ?

The Val is used to coerce text string to a numeric value but returns 0 if inputbox is empty

To do what you want replace this line

If Iret = vbYes Then Cells(ListRow, ScoreColumn) = Val(MyNewScore): i = i + 1: ListRow = ListRow + 1

with this

If Iret = vbYes Then Cells(ListRow, ScoreColumn) = IIf(Val(MyNewScore) > 0, Val(MyNewScore), "'--"): i = i + 1: ListRow = ListRow + 1

Dave

 

[grady121]

Thanks for update Dave, it works fine.

Finally, after studying your code I'm unable to identify where/how you've set up the specific Yes/No vbQuestion Message boxes.
Specifically, how would you make the 'No' button the default option, when asked if the score of more than 40 is OK?

In normal circumstances I would add vbDefaultButton2, but can't suss out where it might go !

Thanks in the meantime
Gray

 

[dmt32]

Thanks for update Dave, it works fine.

Finally, after studying your code I'm unable to identify where/how you've set up the specific Yes/No vbQuestion Message boxes.
Specifically, how would you make the 'No' button the default option, when asked if the score of more than 40 is OK?

In normal circumstances I would add vbDefaultButton2, but can't suss out where it might go !

Thanks in the meantime
Gray

If Val(MyNewScore) > 40 Then Iret = MsgBox(GetPrompt(PlayersName(i, 1), 2, MyNewScore), 292, "Score Check")

When it comes to msgbox I only use the buttons & icons constants numeric values (keeps code looking tidy)
vbDefaultButton2 has a value of 256 so you just add that to 36 I originally had.

the 36 numbered is made up from

vbYesNo = 4
vbQuestion = 32

just add the values together & Adding vbDefaultButton2 (256 ) to get required result.

You can read more in the VBA Helpfile

Dave