• How to Capture Value of Current Date and Time

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » How to Capture Value of Current Date and Time

    Author
    Topic
    #462029

    I am looking for an example of a workbook, with or without VBA code, that automatically inserts the current date in one cell and the current time in a second cell when the contents of a third cell changes.

    IOW, if a badge number is entered into cell A1 (for example), I would like the current date to be inserted in B1 and the current time to be inserted in C1. Note that I want to capture the values of the date and time in B1 and C1 such that they won’t change when the date and time change.

    Hope I’ve made myself clear.

    Thanks, in advance.

    Viewing 0 reply threads
    Author
    Replies
    • #1174529

      Right-click the sheet tab.
      Select View Code from the popup menu.
      Enter or copy/paste the following code into the module that appears.

      Code:
      Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("A1"), Target) Is Nothing Then
      	Application.EnableEvents = False
      	If Range("A1") = "" Then
      	  Range("B1:C1").ClearContents
      	Else
      	  Range("B1") = Date
      	  Range("C1") = Time
      	End If
      	Application.EnableEvents = True
        End If
      End Sub

      Switch back to Excel and try entering something in cell A1.

      • #1174539

        Thanks, as always, Hans.

        Now, to take this one step further, we want to apply this to every cell in column A. If a badge number is entered into any cell in column A, we want to capture the current date and time in columns B and C of that same row.

        How do we amend the code you provided to do this?

        Thanks, again.

        • #1174541

          Like this:

          Code:
          Private Sub Worksheet_Change(ByVal Target As Range)
            Dim oCell As Range
            If Not Intersect(Range("A:A"), Target) Is Nothing Then
          	Application.EnableEvents = False
          	For Each oCell In Intersect(Range("A:A"), Target)
          	  If oCell = "" Then
          		oCell.Offset(0, 1).Resize(1, 2).ClearContents
          	  Else
          		oCell.Offset(0, 1) = Date
          		oCell.Offset(0, 2) = Time
          	  End If
          	Next oCell
          	Application.EnableEvents = True
            End If
          End Sub
          • #1174544

            Thanks so much .

            Have a good night’s rest. You have earned it, as always!

    Viewing 0 reply threads
    Reply To: How to Capture Value of Current Date and Time

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: