Přejít k hlavnímu obsahu

Jak vložit datové razítko do buňky, pokud je zaškrtnuto políčko v aplikaci Excel?

Za normálních okolností zadáváte razítko data pomocí klávesových zkratek v aplikaci Excel. Co takhle vložit do buňky datové razítko pomocí zaškrtávacího políčka v aplikaci Excel? Při zaškrtnutí políčka se časové razítko automaticky vloží do zadané buňky. Tento článek vám pomůže jej vyřešit.

Vložte datové razítko do buňky, pokud je zaškrtnuto políčko s kódem VBA


Vložte datové razítko do buňky, pokud je zaškrtnuto políčko s kódem VBA

Tato část představí skript VBA, který vám pomůže automaticky vložit datové razítko do buňky, pokud zaškrtnete políčko v aplikaci Excel. Postupujte prosím následovně.

1. Po vložení zaškrtávacího políčka stiskněte Další + F11 současně otevřete Microsoft Visual Basic pro aplikace okno.

2. V Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko Vložit > Modul. Poté zkopírujte a vložte níže uvedený kód VBA do okna modulu.

Kód VBA: Vložte datové razítko do buňky, pokud je zaškrtnuto

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. lis Další + Q klávesy pro zavření Microsoft Visual Basic pro aplikace okno.

4. Klikněte pravým tlačítkem na zaškrtávací políčko a vyberte Přiřadit Micro z nabídky pravého tlačítka myši. Viz screenshot:

5. V Přiřadit makro dialogové okno vyberte CheckBox_Date_Stamp v Název makra a poté klikněte na OK knoflík. Viz screenshot:

Po zaškrtnutí tohoto políčka se datumové razítko vloží automaticky do sousední buňky.


Související články:

Nejlepší nástroje pro produktivitu v kanceláři

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar    Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky    Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

karta kte 201905


Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
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