Excel macro button for timestamp

MattSellors 

Creating a macro button that inserts a timestamp and then disappears can be achieved by using VBA (Visual Basic for Applications) in Excel. Here’s a step-by-step guide to accomplish this:

Step 1: Create Buttons for Each Runner

1. Open your Excel workbook.
2. Go to the Developer tab (if you don't see the Developer tab, you'll need to enable it via File > Options > Customize Ribbon).
3. Click on Insert in the Controls group, then choose Button (Form Control).
4. Draw the button on the worksheet. You will be prompted to assign a macro. Click New to create a new macro.

Step 2: Write the VBA Code

1. When the VBA editor opens, you’ll see a new subroutine. Replace the content with the following code:

Vba Code

Sub InsertTimestampAndRemoveButton()
    Dim btn As Button
    Dim targetCell As Range

    ' Get the button that called the macro
    Set btn = ActiveSheet.Buttons(Application.Caller)
    
    ' Determine the target cell based on button position
    Set targetCell = btn.TopLeftCell.Offset(0, 1) ' Adjust the offset if necessary

    ' Insert the timestamp in the target cell
    targetCell.Value = Format(Now, "hh:mm:ss")

    ' Remove the button
    btn.Delete
End Sub

Step 3: Assign the Macro to Each Button

1. For each button you create, assign the InsertTimestampAndRemoveButton macro.
2. Repeat the button creation process for each runner, assigning the same macro to each button.

Step 4: Adjust the Button Position and Cell Reference

• The Offset(0, 1) part of the code means the timestamp will be placed in the cell directly to the right of the button. Adjust the offset values if the target cell is in a different position relative to the button.

Example of Placing Multiple Buttons

1. Place buttons next to each runner’s row:
• Runner 1: Place button in cell A2.
• Runner 2: Place button in cell A3.
• And so on...

Each button, when clicked, will insert the timestamp in the cell to its right and then disappear.

Complete Example

Here’s how it looks in practice:

1. Worksheet Setup:
• Column A: Buttons
• Column B: Runner Numbers
• Column C: Timestamps (target cells)
2. VBA Code:

Vba Code

Sub InsertTimestampAndRemoveButton()
    Dim btn As Button
    Dim targetCell As Range

    ' Get the button that called the macro
    Set btn = ActiveSheet.Buttons(Application.Caller)
    
    ' Determine the target cell based on button position
    Set targetCell = btn.TopLeftCell.Offset(0, 2) ' Adjust the offset if necessary

    ' Insert the timestamp in the target cell
    targetCell.Value = Format(Now, "hh:mm:ss")

    ' Remove the button
    btn.Delete
End Sub

In this example, the timestamp is inserted two columns to the right of the button (i.e., Column C). Adjust the offset to match your exact layout.

Summary

• Create multiple buttons for each runner.
• Assign the same macro to all buttons.
• Adjust the cell reference in the VBA code to ensure timestamps are placed in the correct cells.
• Clicking a button will insert the timestamp in the designated cell and then remove the button.

This setup allows you to efficiently record timestamps with a single click for each runner.

Example can be found in the attached file.

The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.