Preskoči na glavno vsebino

Kako vstaviti datumski žig v celico, če je v Excelu označeno potrditveno polje?

Datumski žig običajno vnesete z bližnjičnimi tipkami v Excelu. Kaj pa vstavite datumski žig v celico s potrditvenim poljem v Excelu? Ko označite potrditveno polje, se časovni žig samodejno vstavi v določeno celico. Ta članek vam bo pomagal rešiti to težavo.

Vstavite datumski žig v celico, če je označeno potrditveno polje s kodo VBA

Vstavite datumski žig v celico, če je označeno potrditveno polje s kodo VBA

Ta razdelek bo predstavil skript VBA, ki vam bo pomagal samodejno vstaviti datumski žig v celico, če je v Excelu označeno potrditveno polje. Naredite naslednje.

1. Ko vstavite potrditveno polje, pritisnite druga + F11 tipke hkrati, da odprete Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Moduli. Nato kopirajte in prilepite spodnjo kodo VBA v okno modula.

Koda VBA: Če je polje označeno, v celico vstavite datumski žig

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. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

4. Z desno miškino tipko kliknite potrditveno polje in izberite Dodeli Micro v meniju z desnim klikom. Oglejte si posnetek zaslona:

5. V Ljubljani Dodeli makro pogovorno okno, izberite CheckBox_Date_Stamp v Ime makra in nato kliknite OK . Oglejte si posnetek zaslona:

Ko označite potrditveno polje, se datumski žig samodejno vstavi v sosednjo celico.

Sorodni članki:

Najboljša pisarniška orodja za produktivnost

🤖 Kutools AI Aide: Revolucionirajte analizo podatkov na podlagi: Inteligentna izvedba   |  Ustvari kodo  |  Ustvarite formule po meri  |  Analizirajte podatke in ustvarite grafikone  |  Prikličite funkcije Kutools...
Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup  |   Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule   |  Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic   |  Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij:  12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

Opis

Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (22)
No ratings yet. Be the first to rate!
Ahmed Hassan
Ahmed Hassan
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
Hash
This comment was minimized by the moderator on the site
Thank you very much!! Brilliant! Thank you!
Reply
0
0
steve
steve
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
Cj
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
Amy H
Amy H
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!
Reply
0
0
crystal
crystal     Amy H
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
Reply
Report
0
0
Mallory V
Mallory V
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. ?
Reply
0
0
Jacob W
Jacob W     Mallory V
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
Reply
0
0
CW
CW     Mallory V
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!
Reply
0
0
Kev
Kev     CW
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!
Reply
0
0
crystal
crystal     CW
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.
Reply
Report
0
0
June
June
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.
Reply
0
0
crystal
crystal     June
This comment was minimized by the moderator on the site
Good Day,
Please assign the macro individually to each checkbox.
Reply
Report
0
0
Miriam Alfy
Miriam Alfy     June
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
Reply
0
0
Sarah T
Sarah T
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!
Reply
0
0
crystal
crystal     Sarah T
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
Reply
Report
0
0
Benoit T
Benoit T     Sarah T
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
Reply
0
0
Ines
Ines
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)
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations