Excel macro: stamping the current date and time

Asked By 10 points N/A Posted on -
qa-featured

Hi out there,

I need a macro that can stamp the current date and time. I would like a button put in so that I can take the stamped time, and copy it to key points in my excel file. Please help on how doing this.

Thanks.

SHARE
Answered By 0 points N/A #110841

Excel macro: stamping the current date and time

qa-featured

 Hi Maggie,

To answer your question quickly, here is the code to get the current timestamp.

Private Sub cbTimeStamp_Click ()

Range ("E2") = Now ()

End Sub

In case this is the your first time working with macros, please see below for the detailed instructions.

1. Go to the Developer Tab.

If the Developer tab is not displayed. Go to Excel Options and in the Popular category tick the Show Developer Tab in the Ribbon.

2. Click Insert and then select Command Button under ActiveX controls.

3. Click on the desired button location and you may resize the button if you want.

.

4. To resize the button, click on one of the white circles and then drag them to get the desired size..

5. You can also change the button properties. To do this right click the button and then select Properties.

 

6. For this example let's change the command button Name and Caption. The Name is used by the program to identify the button during code execution while the Caption is the label written on the command button. I'll use cbTimeStamp for the Name and Time Stamp for the Caption. Close the Properties window after creating the changes.

Here is the output after changing the property.

Caption

Name – You can see this in the Visual Basic Editor and the next step will guide you through that.

7. Make sure that the Design Mode button is enabled and then Double Click your command button ("Time Stamp") to go to the Visual Basic Editor.

 

When you open the editor you will immediately see that there is already a code written. Inside this code you will need to write the actions to be executed by the program. Which in this case, get the current timestamp.

 8. After opening the editor, copy the code below and then paste it in the editor.
Range ("E2") = Now ()

The code should look like this.

"E2" is the cell where the output will be written so you may also change this to any cell you want.

9. Go back to the excel sheet and then untick the Design Mode. This step is important in order for the command button to execute the code. If the design mode is still left enabled and then you click the button, excel will just direct you to the Visual Basic Editor.

10. Finally, click the button to show the result.

 

 Cell E2 shows the current timestamp.

Does this answer your question? Let me know if you need more help on this.

Best Regards,

Ben

Related Questions