Skip to main content

How to insert date stamp into a cell if ticked a checkbox in Excel?

Normally, you enter a date stamp by using hotkeys in Excel. How about insert date stamp into a cell by a checkbox in Excel? When ticking the checkbox, the time stamp is inserted into a specified cell automatically. This article will help you to solve it.

Insert date stamp into a cell if ticked a checkbox with VBA code

Insert date stamp into a cell if ticked a checkbox with VBA code

This section will introduce a VBA script to help you insert a date stamp into a cell automatically if ticked a checkbox in Excel. Please do as follows.

1. After inserting a check box, press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Module window.

VBA code: Insert date stamp into a cell if ticked a checkbox

Sub CheckBox_Date_Stamp()
Dim xChk As CheckBox
Set xChk = ActiveSheet.CheckBoxes(Application.Caller)
With xChk.TopLeftCell.Offset(, 1)
    If xChk.Value = xlOff Then
        .Value = ""
    Else
       .Value = Date
    End If
End With
End Sub

3. Press Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Right click the check box, and select Assign Micro from the right-clicking menu. See screenshot:

5. In the Assign Macro dialog box, select CheckBox_Date_Stamp in the Macro name box, and then click the OK button. See screenshot:

When ticking the check box, the date stamp will be inserted into the adjacent cell automatically.

Related articles:

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...

Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel  (Full-Featured 30-Day Free Trial)

kte tab 201905

60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)
60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 
 
Comments (22)
No ratings yet. Be the first to rate!
Ahmed Hassan
Ahmed Hassan
This comment was minimized by the moderator on the site
Hi, how can I loop the code to include all checkboxes in the column ?
Reply
0
0
crystal
crystal     Ahmed Hassan
This comment was minimized by the moderator on the site
Good day,
If there are lots of checkboxes in rows and you want to act on all check boxes at the same time, the below VBA code can help you.
Please copy the codes below into a Module code window, go back to the worksheet and create a button (such as a Button (Form Control)), assign the macro SetAllChkChange() to the button, then click the button to run the code.

Now all check boxes in rows of your worksheet have been activate. You can check any one of them to insert date stamp in its adjacent cell.

<div data-tag="code">Sub SetAllChkChange()
'Updated by Extendoffice 20211130
Dim xChks
Dim xChk As CheckBox
On Error Resume Next
Set xChks = ActiveSheet.CheckBoxes
For Each xChk In xChks
xChk.Select
Selection.OnAction = "ObjChkChange"
Next
End Sub

Sub ObjChkChange()
Dim xChk As CheckBox
Set xChk = ActiveSheet.CheckBoxes(Application.Caller)
With xChk.TopLeftCell.Offset(, 1)
If xChk.Value = xlOff Then
.Value = ""
Else
.Value = Date
End If
End With
End Sub
Reply
Report
0
0
Hash
Hash
This comment was minimized by the moderator on the site
Thank you very much!! Brilliant! Thank you!
Reply
0
0
steve
steve
This comment was minimized by the moderator on the site
I used the VBA for the checkbox time stamp, but the first two cells are not working correctly. My first check box is in A2, when I check the box, the time is posted in B1. How do I fix this?
Reply
0
0
crystal
crystal     steve
This comment was minimized by the moderator on the site
Hi Steve,
Please replace the fourth line in the code with With xChk.TopLeftCell.Offset(1, 1).
Reply
Report
0
0
Cj
Cj
This comment was minimized by the moderator on the site
hi! i can't seem to find a way where in the datestamp will be on the side of the check box. i tried changing the offset value from 0, 1 and -1. can you help me with this? thank you!
Reply
0
0
crystal
crystal     Cj
This comment was minimized by the moderator on the site
Hi, supposing your check box is in A2 and want to output the datestamp on the right side of the check box (in this case it is B2), please change the Offset value to Offset(1, 1).
Reply
Report
0
0
Mehpire
Mehpire
This comment was minimized by the moderator on the site
how do i get the date stamp to be displayed under my check box?
Reply
Report
0
0
Amy H
Amy H
This comment was minimized by the moderator on the site
Hi! Thanks for the code. It works perfectly with a lil tweaking on the offset. However, i was working on a sheet which has many many manyyy rows (~500+ rows) which contains load of check boxes and the file size grew significantly. Is there any way to reduce the size? Any alternative way to do this?

Thanks!
Reply
0
0
crystal
crystal     Amy H
This comment was minimized by the moderator on the site
Good da
If there are lots of check boxes in rows and you want to act on all check boxes at the same time, the below VBA code can help you.
Please copy the codes into a Module code window, go back to the worksheet and create a button (such as a Button (Form Control)), assign the macro SetAllChkChange() to the button, then click the button to run the code.

Now all check boxes in rows of your worksheet have been activate. You can check any one of them to insert date stamp in its adjacent cell.

Sub SetAllChkChange()
Dim xChks
Dim xChk As CheckBox
Dim xI As Long
On Error Resume Next
Erase xArrChk
Set xChks = ActiveSheet.CheckBoxes
ReDim Preserve xArrChk(1 To xChks.count)
xI = 1
For Each xChk In xChks
xChk.Select
Selection.OnAction = "ObjChkChange"
Next
End Sub


Sub ObjChkChange()
Dim xChk As CheckBox
Set xChk = ActiveSheet.CheckBoxes(Application.Caller)
With xChk.TopLeftCell.Offset(, 1)
If xChk.Value = xlOff Then
.Value = ""
Else
.Value = Date
End If
End With
End Sub
Reply
Report
0
0
Mallory V
Mallory V
This comment was minimized by the moderator on the site
I copy & pasted the VBA code exactly, but on my spreadsheet, the date appears in the cell above and to the right of the check box column, not in the cell directly to the right. ?
Reply
0
0
Jacob W
Jacob W     Mallory V
This comment was minimized by the moderator on the site
This is what I did to fix that issue

Sub CheckBox_Date_Stamp()
Dim xChk As CheckBox
Set xChk = ActiveSheet.CheckBoxes(Application.Caller)
With xChk.TopLeftCell.Offset(1, 1)
If xChk.Value = xlOff Then
.Value = ""
Else
.Value = Now()
End If
End With
End Sub
Reply
0
0
CW
CW     Mallory V
This comment was minimized by the moderator on the site
I am also having that exact same issue. "HELP! I need somebody HELP! Not just anybody HELP! You know I need someone HELP!
Reply
0
0
Kev
Kev     CW
This comment was minimized by the moderator on the site
I had the same issue with the date and time stamp appearing in the cell above the intended cell (using Excel 2007). I went ahead and altered the "offset" formula so that it goes over one cell AND down one cell and now the stamp is appearing where I want it: With xChk.TopLeftCell.Offset(1, 1)
I am updating a worksheet that someone else created and didn't create the checkboxes, but it may have to do with where inside the cell the check box is placed. I got different results when I lined up the checkbox with the bottom of the cell.

Hope that helps!
Reply
0
0
crystal
crystal     CW
This comment was minimized by the moderator on the site
Good day,
The code works well in my case. After checking the check box, the date will appear in the cell directly to the right. Would you provide a screenshot of your case. And which Office version do you use. Thank you.
Reply
Report
0
0
June
June
This comment was minimized by the moderator on the site
HI I tried this formula and it worked only for A1 and B1 when I applied the macro to to the checkbox in A1. However, when I applied the macro to the checkbox in A2, nothing happened in B2. Also how would the formula be changed if I wanted to use this for a checklist? If column A was the checkboxes and column C was completed date.
Reply
0
0
crystal
crystal     June
This comment was minimized by the moderator on the site
Good Day,
Please assign the macro individually to each checkbox.
Reply
Report
0
0
Miriam Alfy
Miriam Alfy     June
This comment was minimized by the moderator on the site
Hi:) you can copy the cell with the checkbox in A1 to the rest of the column. or assign the macro individually to each checkbox
Reply
0
0
Sarah T
Sarah T
This comment was minimized by the moderator on the site
Is there any way to do this with the date AND time? Thanks for the info either way!
Reply
0
0
crystal
crystal     Sarah T
This comment was minimized by the moderator on the site
Hi,
Please apply below VBA code to add date and time.

Sub CheckBox_Date_Stamp()
Dim xChk As CheckBox
Set xChk = ActiveSheet.CheckBoxes(Application.Caller)
With xChk.TopLeftCell.Offset(, 1)
If xChk.Value = xlOff Then
.Value = ""
Else
.Value = Now()
End If
End With
End Sub
Reply
Report
0
0
Benoit T
Benoit T     Sarah T
This comment was minimized by the moderator on the site
Sub CheckBox_Date_Stamp() Dim xChk As CheckBox Set xChk = ActiveSheet.CheckBoxes(Application.Caller) With xChk.TopLeftCell.Offset(, 1) If xChk.Value = xlOff Then .Value = "" Else .Value = Date & " " & Time End If End With End Sub
Reply
0
0
Ines
Ines
This comment was minimized by the moderator on the site
Thank you, this was very helpful, I would like to note that I find it more useful to have the date stamp to the left of the check box. to do this you just change the offset to (, -1)
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations