Saltar al contenido principal

¿Cómo insertar el sello de fecha en una celda si se marcó una casilla de verificación en Excel?

Normalmente, ingresa un sello de fecha usando teclas de acceso rápido en Excel. ¿Qué tal insertar el sello de fecha en una celda con una casilla de verificación en Excel? Al marcar la casilla de verificación, la marca de tiempo se inserta en una celda específica automáticamente. Este artículo te ayudará a resolverlo.

Inserte el sello de fecha en una celda si marcó una casilla de verificación con código VBA


Inserte el sello de fecha en una celda si marcó una casilla de verificación con código VBA

Esta sección presentará una secuencia de comandos VBA para ayudarlo a insertar un sello de fecha en una celda automáticamente si marca una casilla de verificación en Excel. Haz lo siguiente.

1. Después de insertar una casilla de verificación, presione otro + F11 teclas simultáneamente para abrir el Microsoft Visual Basic para aplicaciones ventana.

2. En el Microsoft Visual Basic para aplicaciones ventana, haga clic recuadro > Módulo. Luego copie y pegue el siguiente código VBA en la ventana del Módulo.

Código de VBA: inserte el sello de fecha en una celda si marcó una casilla de verificación

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. Prensa otro + Q llaves para cerrar el Microsoft Visual Basic para aplicaciones ventana.

4. Haga clic con el botón derecho en la casilla de verificación y seleccione Asignar micro desde el menú contextual. Ver captura de pantalla:

5. En el Asignar macro cuadro de diálogo, seleccione CheckBox_Date_Sello existentes Nombre de la macro cuadro, y luego haga clic en el OK botón. Ver captura de pantalla:

Al marcar la casilla de verificación, el sello de fecha se insertará automáticamente en la celda adyacente.


Artículos relacionados:

Las mejores herramientas de productividad de oficina

🤖 Asistente de IA de Kutools: Revolucionar el análisis de datos basado en: Ejecución inteligente   |  Generar codigo  |  Crear fórmulas personalizadas  |  Analizar datos y generar gráficos  |  Invocar funciones de Kutools...
Características populares: Buscar, resaltar o identificar duplicados   |  Eliminar filas en blanco   |  Combine columnas o celdas sin perder datos   |   Ronda sin fórmula ...
Super búsqueda: Búsqueda virtual de criterios múltiples    Búsqueda V de valores múltiples  |   VLookup en varias hojas   |   Búsqueda difusa ....
Lista desplegable avanzada: Crear rápidamente una lista desplegable   |  Lista desplegable dependiente   |  Lista desplegable de selección múltiple ....
Administrador de columnas: Agregar un número específico de columnas  |  Mover columnas  |  Toggle Estado de visibilidad de columnas ocultas  |  Comparar rangos y columnas ...
Características destacadas: Enfoque de cuadrícula   |  Vista de diseño   |   Gran barra de fórmulas    Administrador de hojas y libros de trabajo   |  Biblioteca de Recursos (Texto automático)   |  Selector de fechas   |  Combinar hojas de trabajo   |  Cifrar/descifrar celdas    Enviar correos electrónicos por lista   |  Súper filtro   |   Filtro especial (filtro negrita/cursiva/tachado...) ...
Los 15 mejores conjuntos de herramientas12 Texto Herramientas (Añadir texto, Quitar caracteres, ...)   |   50+ Tabla Tipos (Diagrama de Gantt, ...)   |   40+ Práctico Fórmulas (Calcular la edad según el cumpleaños, ...)   |   19 Inserción Herramientas (Insertar código QR, Insertar imagen desde la ruta, ...)   |   12 Conversión Herramientas (Números a palabras, Conversión de Moneda, ...)   |   7 Fusionar y dividir Herramientas (Filas combinadas avanzadas, Células partidas, ...)   |   ... y más

Mejore sus habilidades de Excel con Kutools for Excel y experimente la eficiencia como nunca antes. Kutools for Excel ofrece más de 300 funciones avanzadas para aumentar la productividad y ahorrar tiempo.  Haga clic aquí para obtener la función que más necesita...

Descripción


Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
Comments (22)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, how can I loop the code to include all checkboxes in the column ?
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
This comment was minimized by the moderator on the site
Thank you very much!! Brilliant! Thank you!
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?
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).
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!
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).
This comment was minimized by the moderator on the site
how do i get the date stamp to be displayed under my check box?
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!
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
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. ?
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
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!
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!
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.
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.
This comment was minimized by the moderator on the site
Good Day,
Please assign the macro individually to each checkbox.
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
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!
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
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
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)
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations