如果在Excel中选中复选框，如何在单元格中插入日期戳？

通常，您可以通过在Excel中使用热键输入日期戳。如何通过Excel中的复选框将日期戳插入单元格中？勾选复选框时，时间戳将自动插入到指定的单元格中。本文将帮助您解决问题。

如果选中带有VBA代码的复选框，则将日期戳插入到单元格中

如果选中带有VBA代码的复选框，则将日期戳插入到单元格中

惊人！在Excel中使用高效选项卡，例如Chrome，Firefox和Safari！

每天节省50％的时间，并减少数千次鼠标单击！

本节将介绍VBA脚本，以帮助您在选中Excel中的复选框时自动将日期戳插入单元格中。请执行以下操作。

1.插入复选框后，按其他 + F11 同时打开 Microsoft Visual Basic应用程序 窗口。

2。在里面 Microsoft Visual Basic应用程序 窗口中，单击插页 > 模块。然后将下面的VBA代码复制并粘贴到“模块”窗口中。

VBA代码：如果选中复选框，则将日期戳插入到单元格中

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。按其他 + Q 关闭键 Microsoft Visual Basic应用程序 窗口。

4.右键单击复选框，然后选择 分配微 从右键单击菜单中。看截图：

5。在里面 分配宏 对话框中选择 CheckBox_Date_Stamp 在宏名框，然后单击 OK 按钮。看截图：

勾选复选框时，日期戳将自动插入到相邻的单元格中。

最佳办公效率工具

Kutools for Excel解决了您的大多数问题，并使您的生产率提高了80％

重用: 快速插入 复杂的公式，图表 以及您以前使用过的任何东西；加密单元带密码创建邮件列表并发送电子邮件...
超级公式栏（轻松编辑多行文本和公式）；阅读版式（轻松读取和编辑大量单元格）；粘贴到过滤范围...
合并单元格/行/列不会丢失数据；拆分单元格内容；合并重复的行/列...防止细胞重复；比较范围...
选择重复或唯一行; 选择空白行（所有单元格都是空的）；超级查找和模糊查找在许多工作簿中；随机选择...
确切的副本多个单元格，无需更改公式参考；自动创建参考到多张纸；插入项目符号，复选框等...
提取文字，添加文本，按位置删除，删除空间; 创建和打印分页小计；在单元格内容和注释之间转换...
超级滤镜（将过滤方案保存并应用于其他工作表）；高级排序按月/周/日，频率及更多；特殊过滤器用粗体，斜体...
结合工作簿和工作表; 根据关键列合并表；将数据分割成多个工作表; 批量转换xls，xlsx和PDF...
超过300种强大功能。支持Office / Excel 2007-2019和365。支持所有语言。在您的企业或组织中轻松部署。完整功能30天免费试用。 60天退款保证。

阅读更多免费下载... 采购...

Office选项卡为Office带来了选项卡式界面，使您的工作更加轻松

在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
每天将您的工作效率提高50％，并减少数百次鼠标单击！

阅读更多免费下载... 采购...

20 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

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.
Hash · 1 years ago
Thank you very much!! Brilliant! Thank you!
- Reply
To post as a guest, your comment is unpublished.
steve · 1 years ago
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
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Steve,
  Please replace the fourth line in the code with With xChk.TopLeftCell.Offset(1, 1).
  
  Reply
To post as a guest, your comment is unpublished.
Cj · 1 years ago
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
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  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
To post as a guest, your comment is unpublished.
Mehpire · 2 years ago
how do i get the date stamp to be displayed under my check box?
- Reply
To post as a guest, your comment is unpublished.
Amy H · 2 years 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!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years 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
  
  Reply
To post as a guest, your comment is unpublished.
Mallory V · 3 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. ?
- Reply
- To post as a guest, your comment is unpublished.
  Jacob W · 3 years 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
  
  Reply
- To post as a guest, your comment is unpublished.
  CW · 3 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!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Kev · 3 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!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 3 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.
  
  Reply
To post as a guest, your comment is unpublished.
June · 4 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.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Good Day,
  Please assign the macro individually to each checkbox.
  
  Reply
- To post as a guest, your comment is unpublished.
  Miriam Alfy · 3 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
  
  Reply
To post as a guest, your comment is unpublished.
Sarah T · 4 years ago
Is there any way to do this with the date AND time? Thanks for the info either way!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 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
  
  Reply
- To post as a guest, your comment is unpublished.
  Benoit T · 4 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
  
  Reply
To post as a guest, your comment is unpublished.
Ines · 4 years ago
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

登录到您的帐户

重设密码