Data Entry Form

floridabill

New Member
Joined
Sep 13, 2018
Messages
3
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
[TABLE="width: 752"]
<tbody>[TR]
[TD="width: 94"]Name[/TD]
[TD="class: xl63, width: 94, align: right"]1/1/2018[/TD]
[TD="class: xl63, width: 94, align: right"]1/8/2018[/TD]
[TD="class: xl63, width: 94, align: right"]1/15/2018[/TD]
[TD="class: xl63, width: 94, align: right"]1/22/2018[/TD]
[TD="class: xl63, width: 94, align: right"]1/29/2018[/TD]
[TD="class: xl63, width: 94, align: right"]2/5/2018[/TD]
[TD="class: xl63, width: 94, align: right"]2/12/2018[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Charles[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Milke[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]27[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Last edited:
Upvote 0
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
Code:
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
 
Upvote 0
Oh man that is really COOL ! Thanks so much!!!!
:laugh::laugh:



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
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top