Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

arrow blue right bubble 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.

arrow blue right bubbleRelated articles:

Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!
Read More Download Now Purchase

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Read More Download Now Purchase
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mehpire · 5 months ago
    how do i get the date stamp to be displayed under my check box?
  • To post as a guest, your comment is unpublished.
    Amy H · 9 months ago
    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!
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      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
  • To post as a guest, your comment is unpublished.
    Mallory V · 1 years ago
    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. ?
    • To post as a guest, your comment is unpublished.
      Jacob W · 10 months ago
      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
    • To post as a guest, your comment is unpublished.
      CW · 1 years ago
      I am also having that exact same issue. "HELP! I need somebody HELP! Not just anybody HELP! You know I need someone HELP!
      • To post as a guest, your comment is unpublished.
        Kev · 1 years ago
        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!
      • To post as a guest, your comment is unpublished.
        crystal · 1 years ago
        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.
  • To post as a guest, your comment is unpublished.
    June · 1 years ago
    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.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Please assign the macro individually to each checkbox.
    • To post as a guest, your comment is unpublished.
      Miriam Alfy · 1 years ago
      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
  • To post as a guest, your comment is unpublished.
    Sarah T · 2 years ago
    Is there any way to do this with the date AND time? Thanks for the info either way!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      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
    • To post as a guest, your comment is unpublished.
      Benoit T · 1 years ago
      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
Japanese 日本語
en Englishaf Afrikaanssq Shqipar العربيةhy Հայերենaz Azərbaycan dilieu Euskarabe Беларуская моваbg Българскиca Catalàzh-CN 简体中文zh-TW 繁體中文hr Hrvatskics Čeština‎da Dansknl Nederlandset Eestitl Filipinofi Suomifr Françaisgl Galegoka ქართულიde Deutschel Ελληνικάht Kreyol ayisyeniw עִבְרִיתhi हिन्दीhu Magyaris Íslenskaid Bahasa Indonesiaga Gaeligeit Italianoja 日本語ko 한국어lv Latviešu valodalt Lietuvių kalbamk Македонски јазикms Bahasa Melayumt Malteseno Norsk bokmålfa فارسیpl Polskipt Portuguêsro Românăru Русскийsr Српски језикsk Slovenčinasl Slovenščinaes Españolsw Kiswahilisv Svenskath ไทยtr Türkçeuk Українськаur اردوvi Tiếng Việtcy Cymraegyi יידיש