Loncat ke daftar isi utama

Bagaimana cara menambahkan / memasukkan tanggal / waktu saat ini secara otomatis dalam sel dengan mengklik ganda di Excel?

Jika Anda perlu sering memasukkan tanggal atau tanggal saat ini di lembar kerja, Anda dapat mencoba metode di artikel ini. Artikel ini akan membantu Anda untuk secara otomatis menambahkan atau memasukkan tanggal atau tanggal saat ini dalam sel rentang tertentu hanya dengan mengklik ganda.

Klik dua kali untuk menambahkan / memasukkan tanggal atau tanggal secara otomatis dengan kode VBA

Klik dua kali untuk menambahkan / memasukkan tanggal atau tanggal secara otomatis dengan kode VBA

Anda dapat menjalankan kode VBA di bawah ini untuk secara otomatis menambahkan tanggal atau waktu tanggal saat ini dalam sel dengan mengklik ganda. Silakan lakukan sebagai berikut.

1. Klik kanan tab Lembar yang ingin Anda masukkan tanggal sekarang ke dalam sel yang ditentukan, lalu pilih Lihat kode dari menu klik kanan.

2. Dalam Microsoft Visual basic untuk Aplikasi jendela, salin dan tempel kode VBA di bawah ini ke jendela Kode.

Kode VBA: Klik dua kali untuk menambahkan tanggal sekarang ke dalam sel

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
        Cancel = True
        Target.Formula = Date
    End If
End Sub

Catatan:

1. Dalam kode, A1: B10 adalah rentang yang akan Anda tambahkan tanggal sekarang.
2. Jika Anda perlu menambahkan waktu tanggal sekarang ke dalam sel, harap ganti Tanggal dengan Sekarang() di dalam kode. Anda dapat mengubahnya sesuai kebutuhan.

3. tekan lain + Q tombol secara bersamaan untuk menutup Microsoft Visual Basic untuk Aplikasi jendela dan kembali ke lembar kerja.

Mulai sekarang, saat mengklik ganda sel mana pun dalam rentang tertentu A1: B10. Tanggal atau waktu tanggal saat ini akan dimasukkan secara otomatis.

Terkait artikel:

Alat Produktivitas Kantor Terbaik

Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Canggih untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

tab kte 201905

Office Tab Menghadirkan antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (28)
No ratings yet. Be the first to rate!
eder109
eder109
This comment was minimized by the moderator on the site
Hi all,

I try to use that macro to use the date stamp double clicking on column E and it's working but when I try to replicate the macro to do the same but for the current time on column F it is not working as you can see attached I have an error message stating : Ambiguous Name Detected.
When I try to change the Sub WorkSheet part for another name and double click in the cells nothing happens.

Could someone help me on that ?

My code :


Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("E1:E10000")) Is Nothing Then
Cancel = True
Target.Formula = Date
End If
End Sub

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("F1:F10000")) Is Nothing Then
Cancel = True
Target.Formula = Now()
End If
End Sub
View Attachments (1 / 5)
Reply
Report
0
0
crystal
crystal     eder109
This comment was minimized by the moderator on the site
Hi Louis,
Replicate the macro will cause two same procedures with the same name in a single sheet code window. Excel doesn't allow two or more same names of functions in a module. Not even in Events. It leads to ambiguity.
If you want to do a different task on the same event, you need to modify the original code to meet your needs.
The following VBA code can do you a favor. Please give it a try.
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updated by Extendoffice 20221025
    If Not Intersect(Target, Range("E1:E10000")) Is Nothing Then
        Cancel = True
        Target.Formula = Date
    End If
    If Not Intersect(Target, Range("F1:F10000")) Is Nothing Then
        Cancel = True
        Target.Formula = Date
    End If
End Sub
Reply
Report
0
0
Bob
Bob
This comment was minimized by the moderator on the site
This function did not work. Double Clicking simply enters manual edit of cell.
Reply
0
0
crystal
crystal     Bob
This comment was minimized by the moderator on the site
Hi Bob,
The code works well in my case. I need to know more specific about your issue, such as your Excel version.
And the code only works on the cells you specified.
Reply
Report
0
0
Ahmad
Ahmad
This comment was minimized by the moderator on the site
Hello there, the code did a lot for me, How can I restrict the code to work only if field is blank. If a date is already there in the cell, double click should do nothing, regards
Reply
0
0
crystal
crystal     Ahmad
This comment was minimized by the moderator on the site
Hi Ahmad,
Sorry for the trouble. To only fill in the blank cells with dates with double-clicking, you can apply the following VBA code to get it done.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updated by Extendoffice 20220609
    If Not Intersect(Target, Range("B1:C20")) Is Nothing Then
        If Target.Value = "" Then
            Cancel = True
            Target.Formula = Date
        End If
    End If
End Sub
Reply
Report
0
0
Joe
Joe
This comment was minimized by the moderator on the site
This was just what I was looking for - this save a ton of time and I appreciate the well written instructions. Thank you!
Reply
0
0
Lee Hoemann
Lee Hoemann
This comment was minimized by the moderator on the site
So I inserted code and it works great on several sheets in my workbook, however on some sheets it just suddenly stops working after a certain row even though I have the correct range entered. Any thoughts on why this might happen.
Reply
0
0
Heather
Heather
This comment was minimized by the moderator on the site
Does anyone know if there is a way to insert this code into Excel Online? I had used it with the desktop version and it worked great but now we have migrated everything to the online platform and my date and time stamps on double click have disappeared and I can't figure out how to view or edit the code. Thanks.
Reply
Report
0
0
Dylan
Dylan
This comment was minimized by the moderator on the site
Love the code and it works great. How can I make it so when I double click to execute the code its shows time in military time?
Reply
0
0
Heather
Heather     Dylan
This comment was minimized by the moderator on the site
I would think that if you just select the Military Time format for that cell from the Number -> Time format options that should do it. For example, you would select 13:30 instead of 1:30 PM, and then it should display in military time.
Reply
Report
0
0
Heather
Heather     Dylan
This comment was minimized by the moderator on the site
I think if you select the military time format for that cell from the Format -> Number -> Time options in your sheet that ought to work. For example, it gives the option of 1:30 PM or 13:30, so you would just select 13:30 and that should do it.
Reply
0
0
crystal
crystal     Dylan
This comment was minimized by the moderator on the site
Hi Dylan,
Sorry can't help you with that yet. Thank you for your comment.
Reply
Report
0
0
Max
Max
This comment was minimized by the moderator on the site
Hi there,

I copied and pasted the above code exactly as it is written into a blank workbook, however, it does not work for me. I looked at different sources on the web and most sites have a similar format as what is written above. I think perhaps there is something wrong with my VBA or some settings are not turned on. Any advice would be much appreciated. I am running Excel for Office 365 MSO (16.0.11001.20097) 32-bit on Windows 10.
Reply
0
0
Trav
Trav
This comment was minimized by the moderator on the site
Hello, the above code worked great for me. Now i am just wondering if there is a way to have the text "Double click to enter date" appear in the cell until the date is entered. My goal is to make the document be as user friendly as possible. Thank you in advance
Reply
Report
0
0
crystal
crystal     Trav
This comment was minimized by the moderator on the site
Hi Travis,
We can’t modify the code to have text display in the cell directly. But alternatively, the below optimized code will help to display the text in the cell comment, and the comment will be removed automatically after double click the cell to enter date.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
Target.NoteText "double click to add date"
End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
Cancel = True
Target.Comment.Delete
Target.Formula = Date
End If
End Sub
Reply
Report
0
0
Tee
Tee
This comment was minimized by the moderator on the site
Hello, the code you gave works great. I am just curious if there is any way to have the text "double click to add date" In the cell until the date is entered. Thank you in advance ( I am trying to make my document as user friendly as possible as to not confuse my co-workers)
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations