Data Entry Form

[floridabill]

I have a list og Golfers , each we we record their points to determine their rank for the following week . Currently we open a spreadsheet and enter their scores for the week , the spreadsheet calculates the average from the top 3 scores from the five most recent scores. All of this works fine.

I want to create a Form that finds the next empty column in each player's Row allows me to type in the number and select the next player and enter his scores.

this exaple is hoe the sheet is laid out

Name	1/1/2018	1/8/2018	1/15/2018	1/22/2018	1/29/2018	2/5/2018	2/12/2018
Bill	20	21	22	23	20	22
Charles	15	16	18	14	26	29
Milke	20	21	23	25	28	27
Bob	15	14	15	16	14	15
Jim	28	14	17	19	22	23

<tbody>
</tbody>

I ca't even get past the first part of the code that i typed so far -- frankly I'm lost . i copied code that added to the last empty row and tried modifying it
any help would be appreciated . getting a 424 error code

Private Sub CommandButton1_Click()
lastcol = Cells(9, colums.Count).End(x1Toleft)
lastcol = Cells(9, Columns.Count).Value = "wmKnott"
End Sub

 

[My Aswer Is This]

You said:

I want to create a Form that finds...

Define Form

Do you mean a UserForm.

And what is

wmKnott​

Or you would need to have A Activex Combobox
We could load all the players names into the combobox

Then You could enter the score into a Activex Textbox

Then when you choose a name in the Combobox a script would run to add the score to the first empty cell in the row with the players name.

Would you want something like that or do you want to use a Userform?

If you have never built a UserForm it would take a little knowledge how to do that and write all the code needed.

Or we could have you enter the score into say Range("A1")

And then when you double click on the players name the score you entered into Range("A1") would be entered.

Pease advise what solution you may like.
The double click plan would be easy way for you we could provide all the code you need.
Just tell me where you will enter the score.

You could use a Activex textbox or we could have a Inputbox pop up asking for you to enter the score.

I'm assuming the names are in column A Starting in Row(2)

Their are always 20 ways to do almost anything using Excel.
​

 

[My Aswer Is This]

Here is the easy way to do this.
With this script when you double click on a Players name in column A starting on row 2
You will get a Input box popup asking you for the players score.
Enter the score press OK and the score will be entered.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  9/13/2018  10:25:28 PM  EDT
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If Not Intersect(Target, Range("A2:A" & Lastrow)) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim LastColumn As Long
Dim ans As String
LastColumn = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
ans = InputBox("Enter score For  " & Target.Value)
Cells(Target.Row, LastColumn).Value = ans
End If
End Sub

 

[floridabill]

Oh man that is really COOL ! Thanks so much!!!!

Here is the easy way to do this.
With this script when you double click on a Players name in column A starting on row 2
You will get a Input box popup asking you for the players score.
Enter the score press OK and the score will be entered.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  9/13/2018  10:25:28 PM  EDT
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If Not Intersect(Target, Range("A2:A" & Lastrow)) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim LastColumn As Long
Dim ans As String
LastColumn = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
ans = InputBox("Enter score For  " & Target.Value)
Cells(Target.Row, LastColumn).Value = ans
End If
End Sub

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Oh man that is really COOL ! Thanks so much!!!!