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
|
|||
|
|||
automate date entry - update pls.
hi gord.
this was great code but i need range to be a1:aj1 and date to be in ak. i have over 4000 rows that i need date to always appear in col ak. how do i change code? thanks. "Gord Dibben" wrote: medavino Right-click on the worksheet tab and "View Code" Copy/paste this code into that sheet module. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If .Value "" Then .Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub Any time you make a change in a cell in A1:A10 range, the date/time will appear in corresponding cell in column B Gord Dibben MS Excel MVP i am using excel 2003 and need to automatically insert date into a cell when i update info in a row. others need to know how current data in a row is. any thoughts out there? please keep in mind that although not a total beginner, certain areas in excel i have never used. thanks in advance. |
#2
|
|||
|
|||
automate date entry - update pls.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A:AJ")) Is Nothing Then With Target If .Value "" Then Me.Cells(.Row, "AK").Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "medavino" wrote in message ... hi gord. this was great code but i need range to be a1:aj1 and date to be in ak. i have over 4000 rows that i need date to always appear in col ak. how do i change code? thanks. "Gord Dibben" wrote: medavino Right-click on the worksheet tab and "View Code" Copy/paste this code into that sheet module. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If .Value "" Then .Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub Any time you make a change in a cell in A1:A10 range, the date/time will appear in corresponding cell in column B Gord Dibben MS Excel MVP i am using excel 2003 and need to automatically insert date into a cell when i update info in a row. others need to know how current data in a row is. any thoughts out there? please keep in mind that although not a total beginner, certain areas in excel i have never used. thanks in advance. |
#3
|
|||
|
|||
automate date entry - update pls.
bob,
I rec'd a compile error msg and highlight of this statement - " Me.Cells(.Row, "AK").Value = Format(Now, "dd mmm yyyy" i don't know enough about programming to understand. pls help. also, i need the date not to update when s/s is calculated or opened. i thought the Now function would change? thanks. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A:AJ")) Is Nothing Then With Target If .Value "" Then Me.Cells(.Row, "AK").Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "medavino" wrote in message ... hi gord. this was great code but i need range to be a1:aj1 and date to be in ak. i have over 4000 rows that i need date to always appear in col ak. how do i change code? thanks. "Gord Dibben" wrote: medavino Right-click on the worksheet tab and "View Code" Copy/paste this code into that sheet module. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If .Value "" Then .Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub Any time you make a change in a cell in A1:A10 range, the date/time will appear in corresponding cell in column B Gord Dibben MS Excel MVP i am using excel 2003 and need to automatically insert date into a cell when i update info in a row. others need to know how current data in a row is. any thoughts out there? please keep in mind that although not a total beginner, certain areas in excel i have never used. thanks in advance. |
#4
|
|||
|
|||
automate date entry - update pls.
As I read you want the date to change in column AK anytime data is changed in a
row from columns A to AJ for 4000 rows. You sure you want to do this? Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:AJ4000")) Is Nothing Then With Target If .Value "" Then Me.Range("AK" & Target.Row).Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 27 Dec 2007 10:27:04 -0800, medavino wrote: hi gord. this was great code but i need range to be a1:aj1 and date to be in ak. i have over 4000 rows that i need date to always appear in col ak. how do i change code? thanks. "Gord Dibben" wrote: medavino Right-click on the worksheet tab and "View Code" Copy/paste this code into that sheet module. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If .Value "" Then .Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub Any time you make a change in a cell in A1:A10 range, the date/time will appear in corresponding cell in column B Gord Dibben MS Excel MVP i am using excel 2003 and need to automatically insert date into a cell when i update info in a row. others need to know how current data in a row is. any thoughts out there? please keep in mind that although not a total beginner, certain areas in excel i have never used. thanks in advance. |
#5
|
|||
|
|||
automate date entry - update pls.
There was an unfortunate line break in the post. Try plopping in this portion:
If .Value "" Then Me.Cells(.Row, "AK").Value _ = Format(Now, "dd mmm yyyy hh:mm:ss") End If medavino wrote: bob, I rec'd a compile error msg and highlight of this statement - " Me.Cells(.Row, "AK").Value = Format(Now, "dd mmm yyyy" i don't know enough about programming to understand. pls help. also, i need the date not to update when s/s is calculated or opened. i thought the Now function would change? thanks. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A:AJ")) Is Nothing Then With Target If .Value "" Then Me.Cells(.Row, "AK").Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "medavino" wrote in message ... hi gord. this was great code but i need range to be a1:aj1 and date to be in ak. i have over 4000 rows that i need date to always appear in col ak. how do i change code? thanks. "Gord Dibben" wrote: medavino Right-click on the worksheet tab and "View Code" Copy/paste this code into that sheet module. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If .Value "" Then .Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub Any time you make a change in a cell in A1:A10 range, the date/time will appear in corresponding cell in column B Gord Dibben MS Excel MVP i am using excel 2003 and need to automatically insert date into a cell when i update info in a row. others need to know how current data in a row is. any thoughts out there? please keep in mind that although not a total beginner, certain areas in excel i have never used. thanks in advance. -- Dave Peterson |
#6
|
|||
|
|||
automate date entry - update pls.
YAHOO. i don't understand it but it works !!!
thank you to each of you for your input. regards maureen (medavino) "Dave Peterson" wrote: There was an unfortunate line break in the post. Try plopping in this portion: If .Value "" Then Me.Cells(.Row, "AK").Value _ = Format(Now, "dd mmm yyyy hh:mm:ss") End If medavino wrote: bob, I rec'd a compile error msg and highlight of this statement - " Me.Cells(.Row, "AK").Value = Format(Now, "dd mmm yyyy" i don't know enough about programming to understand. pls help. also, i need the date not to update when s/s is calculated or opened. i thought the Now function would change? thanks. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A:AJ")) Is Nothing Then With Target If .Value "" Then Me.Cells(.Row, "AK").Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "medavino" wrote in message ... hi gord. this was great code but i need range to be a1:aj1 and date to be in ak. i have over 4000 rows that i need date to always appear in col ak. how do i change code? thanks. "Gord Dibben" wrote: medavino Right-click on the worksheet tab and "View Code" Copy/paste this code into that sheet module. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If .Value "" Then .Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub Any time you make a change in a cell in A1:A10 range, the date/time will appear in corresponding cell in column B Gord Dibben MS Excel MVP i am using excel 2003 and need to automatically insert date into a cell when i update info in a row. others need to know how current data in a row is. any thoughts out there? please keep in mind that although not a total beginner, certain areas in excel i have never used. thanks in advance. -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|