Hello All,

I am currently building a spreadsheet with 6 simple column headings:

Employee
Inventory Number
Quantity
Production Unit
Date & Time
Comments

Problem: I would like to set up the €œDate & Time€� column to where when any
data is entered into a field under the first column (Employee), the
corresponding field under the Date & Time column auto populates the current
date and time. (Obviously to minimize data entry for the employees)

One additional problem: Once that Date & Time field auto populates with the
current time it needs to remain with that time and not update.

Thank-you for any help.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
N = Target.Row
If Me.Range("A" & N).Value < "" Then
Me.Range("G" & N).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Sat, 11 Apr 2009 10:10:02 -0700, mjjohnso
wrote:

Hello All,

I am currently building a spreadsheet with 6 simple column headings:

Employee
Inventory Number
Quantity
Production Unit
Date & Time
Comments

Problem: I would like to set up the “Date & Time” column to where when any
data is entered into a field under the first column (Employee), the
corresponding field under the Date & Time column auto populates the current
date and time. (Obviously to minimize data entry for the employees)

One additional problem: Once that Date & Time field auto populates with the
current time it needs to remain with that time and not update.

Thank-you for any help.

You can use an event macro.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the code window that just opened.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Long

If Target.Cells.Count 1 Then
Exit Sub 'one cell a time
End If

If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub 'Look in column A only
End If

myRow = Target.Row

On Error GoTo ErrHandler:
With Me.Cells(myRow, "d")
If IsEmpty(.Value) Then
'ok to add, the cell is empty
Application.EnableEvents = False
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End If
End With

ErrHandler:
Application.EnableEvents = True

End Sub

Then back to excel and type something into column A where column D is empty.



mjjohnso wrote:

Hello All,

I am currently building a spreadsheet with 6 simple column headings:

Employee
Inventory Number
Quantity
Production Unit
Date & Time
Comments

Problem: I would like to set up the €œDate & Time€� column to where when any
data is entered into a field under the first column (Employee), the
corresponding field under the Date & Time column auto populates the current
date and time. (Obviously to minimize data entry for the employees)

One additional problem: Once that Date & Time field auto populates with the
current time it needs to remain with that time and not update.

Thank-you for any help.

--

Dave Peterson

Hi

You will need a macro to do that.

I assume you have Employee name in column A and Date/Time in column E.

Right click on the sheet tab and select View Code, and insert the code
below in the codesheet which appear. Close the VBA window an enter a
name in column A. Column E is to be formatted as Date/Time

Private Sub Worksheet_Change(ByVal Target As Range)
EmployeeCol = "A"
DateCol = "E"
Set isect = Intersect(Target, Columns(EmployeeCol))
If Not isect Is Nothing Then
TargetRow = Target.Row
Range(DateCol & TargetRow) = Now()
End If
End Sub

Hopes this helps

---
Per

On 11 Apr., 19:10, mjjohnso
wrote:
Hello All,

I am currently building a spreadsheet with 6 simple column headings:

Employee
Inventory Number
Quantity
Production Unit
Date & Time
Comments

Problem: *I would like to set up the “Date & Time” column to where when any
data is entered into a field under the first column (Employee), the
corresponding field under the Date & Time column auto populates the current
date and time. *(Obviously to minimize data entry for the employees) *

One additional problem: *Once that Date & Time field auto populates with the
current time it needs to remain with that time and not update.

Thank-you for any help.

Change this line
With Me.Cells(myRow, "d")
to use the correct column letter. I used D.

Dave Peterson wrote:

You can use an event macro.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the code window that just opened.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Long

If Target.Cells.Count 1 Then
Exit Sub 'one cell a time
End If

If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub 'Look in column A only
End If

myRow = Target.Row

On Error GoTo ErrHandler:
With Me.Cells(myRow, "d")
If IsEmpty(.Value) Then
'ok to add, the cell is empty
Application.EnableEvents = False
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End If
End With

ErrHandler:
Application.EnableEvents = True

End Sub

Then back to excel and type something into column A where column D is empty.

mjjohnso wrote:

Hello All,

I am currently building a spreadsheet with 6 simple column headings:

Employee
Inventory Number
Quantity
Production Unit
Date & Time
Comments

Problem: I would like to set up the €œDate & Time€� column to where when any
data is entered into a field under the first column (Employee), the
corresponding field under the Date & Time column auto populates the current
date and time. (Obviously to minimize data entry for the employees)

One additional problem: Once that Date & Time field auto populates with the
current time it needs to remain with that time and not update.

Thank-you for any help.

--

Dave Peterson

--

Dave Peterson