Skip to main content

Explore the new, free version of Kutools for Outlook today!

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

Author: Siluvia Last Modified: 2020-07-22

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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or 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  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
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

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

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!
Comments (22)
No ratings yet. Be the first to rate!
Ahmed Hassan
Guest
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
Guest
This comment was minimized by the moderator on the site
Thank you very much!! Brilliant! Thank you!
Reply
0
0
steve
Guest
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
Guest
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
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations