Cross Country Timing Issue - VBA/Macros

CycloneSteve

New Member
Joined
Mar 30, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Hi there,

Needing some help with a spreadsheet which is principally designed to record Cross Country runners and tally up the points. I have attempted to integrate a timing feature into the Spreadsheet which by the user clicks on one button in the Score sheet which then records the time at the time of the click (when the race starts). Then as runners cross the line, another button is clicked to record the time they finished and then calculates their finishing time based on the start time and time when the button is clicked. I've copied the code from another spreadsheet but I think I have messed it up as the finishing time ends up in the wrong place. Can I please get some assistance in fixing it. I also would like to use a "hotkey" such as the Caps Lock key on the keyboard to make it easier than having to click on the button each time. I'm quite a noob when it comes to VBA and Macros so I would very much appreciate the help.

Can't use the XL2BB Add-on so posting a link to down the spreadsheet. CC2.xls

Please ask any questions you may need to clarify the problem.

Thanks!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Ok, change the red 5 to a 3, otherwise you are putting the time in col E
Rich (BB code):
    Cells(65536, 5).End(xlUp).Offset(1, 0).Select
Remove this from the Capture_Time code
VBA Code:
    Application.OnKey "{Capslock}", "Capture_Time"
and put
VBA Code:
    Application.OnKey "{Capslock}", "Sheet2.Capture_Time_Click"
in the Start_Timer code
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

CycloneSteve

New Member
Joined
Mar 30, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Ok, change the red 5 to a 3, otherwise you are putting the time in col E
Rich (BB code):
    Cells(65536, 5).End(xlUp).Offset(1, 0).Select
Remove this from the Capture_Time code
VBA Code:
    Application.OnKey "{Capslock}", "Capture_Time"
and put
VBA Code:
    Application.OnKey "{Capslock}", "Sheet2.Capture_Time_Click"
in the Start_Timer code
Thanks, I'll give it a go and see how it turns out.
 

CycloneSteve

New Member
Joined
Mar 30, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Ok, change the red 5 to a 3, otherwise you are putting the time in col E
Rich (BB code):
    Cells(65536, 5).End(xlUp).Offset(1, 0).Select
Remove this from the Capture_Time code
VBA Code:
    Application.OnKey "{Capslock}", "Capture_Time"
and put
VBA Code:
    Application.OnKey "{Capslock}", "Sheet2.Capture_Time_Click"
in the Start_Timer code
Ok, so it seems like the times are being entered into the right places, and the CapsLock hotkey is now working. However I am getting some bizarre times in the times column when I click the button. Plus on the odd occasion I get an error message. Any idea what is causing it??
 

Attachments

  • Capture.PNG
    Capture.PNG
    19.6 KB · Views: 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
That's down to the change event you have running on col C & I have no idea what it's meant to be doing.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,298
Messages
5,641,417
Members
417,209
Latest member
Agbarker

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
Top