If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trigger a Date & Time stamp by entering data in another field...
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. |
#2
|
|||
|
|||
Trigger a Date & Time stamp by entering data in another field...
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. |
#3
|
|||
|
|||
Trigger a Date & Time stamp by entering data in another field...
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 |
#4
|
|||
|
|||
Trigger a Date & Time stamp by entering data in another field...
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. |
#5
|
|||
|
|||
Trigger a Date & Time stamp by entering data in another field...
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 |
Thread Tools | |
Display Modes | |
|
|