Les galetes ens ajuden a lliurar els nostres serveis. En utilitzar els nostres serveis, accepteu el nostre ús de cookies.
Consell: altres idiomes es tradueixen en Google. Pots visitar el English versió d'aquest enllaç.
Iniciar Sessió
x
or
x
x
Registre
x

or

Com s'afegeix automàticament / ingressa la data / hora actual en una cel·la fent doble clic a Excel?

Si necessiteu inserir la data actual o la data amb freqüència en un full de càlcul, podeu provar el mètode d'aquest article. Aquest article us ajudarà a afegir o introduir automàticament la data o la data actual en una cel·la de rang especificada amb només fer doble clic.

Feu doble clic per afegir / escriure actualment la data actual o la data amb el codi VBA


fletxa blau dreta bombolla Feu doble clic per afegir / escriure actualment la data actual o la data amb el codi VBA


Podeu executar el codi VBA de sota per afegir automàticament la data actual o la data en una cel·la fent doble clic. Feu el següent.

1. Feu clic amb el botó dret a la fitxa Full que vulgueu inserir la data actual a les cel·les especificades i, a continuació, seleccioneu Veure codi des del menú del botó dret.

2. A la Microsoft Visual bàsic per a aplicacions finestra, copieu i enganxeu el codi VBA de sota a la finestra del codi.

Codi VBA: feu doble clic per afegir la data actual a una cel·la

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

notes:

1. Al codi, A1: B10 és el rang que agregarà la data actual a.

2. Si heu d'afegir el temps de data actual a la cel·la, reemplace Data amb Ara () en el codi. Podeu canviar-los com necessiteu.

3. Premeu Alt + Q tecles simultàniament per tancar la Microsoft Visual Basic per a aplicacions finestra i tornar al full de treball.

A partir d'ara, quan feu doble clic a qualsevol cel·la del rang especificat A1: B10. La data actual o la data s'incorporaran automàticament.


fletxa blau dreta bombollaArticles relacionats:



Eines de productivitat recomanades

Pestanya d'Office

estrella d'or1 Porteu les pestanyes pràctiques a l'Excel i a un altre programari d'Office, igual que Chrome, Firefox i el nou Internet Explorer.

Kutools for Excel

estrella d'or1 Increïble! Incrementeu la productivitat en 5 minuts. No necessites cap habilitat especial, estalvieu dues hores cada dia.

estrella d'or1 300 Noves característiques per a Excel, Excel molt fàcil i potent:

  • Combina cel·les / files / columnes sense perdre dades.
  • Combina i consolida diverses fulles i llibres.
  • Comparar intervals, copiar diversos rangs, convertir text a data, unitat i conversió de divises.
  • Compte per colors, subtotals de paginació, classificació avançada i filtre súper,
  • Més Seleccioneu / Insereix / Suprimeix / Text / Format / Enllaç / Comentari / Llibres / Eines de full de càlcul ...

Tret de pantalla de Kutools per a Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Max · 3 months ago
    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.
  • To post as a guest, your comment is unpublished.
    Travis · 6 months ago
    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
    • To post as a guest, your comment is unpublished.
      crystal · 6 months ago
      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
  • To post as a guest, your comment is unpublished.
    Tee · 6 months ago
    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)
  • To post as a guest, your comment is unpublished.
    Dre · 9 months ago
    I copied and pasted the code updating the range and it did not work :-(


    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("B4:B100")) Is Nothing Then
    Cancel = True
    Target.Formula = Now()
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 6 months ago
      Good day,
      The code works well in my case. Can you tell me your Office version?
  • To post as a guest, your comment is unpublished.
    Paul · 1 years ago
    The double click entery code created is:


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

    If Not Intersect(Target, Range("b1:b1000")) Is Nothing Then
    Cancel = True
    Target.Formula = Time
    End If

    If Not Intersect(Target, Range("g1:g1000")) Is Nothing Then
    Cancel = True
    Target.Formula = Time
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 11 months ago
      Dear Paul,
      Please try the following VBA code.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRg As Range
      On Error Resume Next
      Set xRg = Intersect(Range("A1:a1000,b1:b1000,G1:G1000"), Target)
      If xRg Is Nothing Then Exit Sub
      Target.Worksheet.Unprotect Password:="123"
      xRg.Locked = True
      Target.Worksheet.Protect Password:="123"
      End Sub

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Application.EnableEvents = False
      ActiveSheet.Unprotect Password:="123"
      If Not Intersect(Target, Range("A1:a1000")) Is Nothing Then
      Cancel = True
      Target.Formula = Date
      End If
      If Not Intersect(Target, Range("b1:b1000")) Is Nothing Then
      Cancel = True
      Target.Formula = Time
      End If
      If Not Intersect(Target, Range("g1:g1000")) Is Nothing Then
      Cancel = True
      Target.Formula = Time
      End If
      ActiveSheet.Protect Password:="123"
      Application.EnableEvents = True
      End Sub