Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Carol W.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Carol W.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks so much .

    Have a good night's rest. You have earned it, as always!
    Carol W.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •