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
|
|||
|
|||
Automatic Date Aupdate
I want to set row above that automatic change date when i change data on each
of the column. For example Last Update: 6/14/2009 5/19/2009 5/19/2009 (2) 3 4 4 5 6 I want if i change 2 to 100 then 6/14/2009 should change to that modification day date automatically. If i change it today then 6/14/2009 will change to 06/23/2009 Meaning this will be modification date. please can anybody help me with this. Appreciate your time. Thanks. |
#2
|
|||
|
|||
Automatic Date Aupdate
Hi,
You would need to use VBA code for that. Something on the order of: Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Range("A22")) If Not isect Is Nothing Then 'Your code here End If End Sub 1. To add this code to your file, press Alt+F11, 2. In the VBAProject window, top left side, find your sheet name under your file name and double click it. 3. Paste in or type the code above. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Kanmi" wrote: I want to set row above that automatic change date when i change data on each of the column. For example Last Update: 6/14/2009 5/19/2009 5/19/2009 (2) 3 4 4 5 6 I want if i change 2 to 100 then 6/14/2009 should change to that modification day date automatically. If i change it today then 6/14/2009 will change to 06/23/2009 Meaning this will be modification date. please can anybody help me with this. Appreciate your time. Thanks. |
#3
|
|||
|
|||
Automatic Date Aupdate
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "A22" Dim cell As Range On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value "" Then With Target.Offset(-1, 0) .Value = Format(Now, "mm/dd/yyyy hh:mm:ss") End With End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 23 Jun 2009 08:08:01 -0700, Kanmi wrote: I want to set row above that automatic change date when i change data on each of the column. For example Last Update: 6/14/2009 5/19/2009 5/19/2009 (2) 3 4 4 5 6 I want if i change 2 to 100 then 6/14/2009 should change to that modification day date automatically. If i change it today then 6/14/2009 will change to 06/23/2009 Meaning this will be modification date. please can anybody help me with this. Appreciate your time. Thanks. |
#4
|
|||
|
|||
Automatic Date Aupdate
Where would insert this code?. Thanks "Gord Dibben" wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const WS_RANGE As String = "A22" Dim cell As Range On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value "" Then With Target.Offset(-1, 0) .Value = Format(Now, "mm/dd/yyyy hh:mm:ss") End With End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 23 Jun 2009 08:08:01 -0700, Kanmi wrote: I want to set row above that automatic change date when i change data on each of the column. For example Last Update: 6/14/2009 5/19/2009 5/19/2009 (2) 3 4 4 5 6 I want if i change 2 to 100 then 6/14/2009 should change to that modification day date automatically. If i change it today then 6/14/2009 will change to 06/23/2009 Meaning this will be modification date. please can anybody help me with this. Appreciate your time. Thanks. |
#5
|
|||
|
|||
Automatic Date Aupdate
Right-click on the sheet tab and "View Code"
Copy/paste into that module. Edit the "A22" if necessary. Alt + q to return to the Excel window. Make a change to a cell in the range and see the date/time entered in the cell above. Gord On Tue, 23 Jun 2009 11:35:02 -0700, Kanmi wrote: Where would insert this code?. Thanks "Gord Dibben" wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const WS_RANGE As String = "A22" Dim cell As Range On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value "" Then With Target.Offset(-1, 0) .Value = Format(Now, "mm/dd/yyyy hh:mm:ss") End With End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 23 Jun 2009 08:08:01 -0700, Kanmi wrote: I want to set row above that automatic change date when i change data on each of the column. For example Last Update: 6/14/2009 5/19/2009 5/19/2009 (2) 3 4 4 5 6 I want if i change 2 to 100 then 6/14/2009 should change to that modification day date automatically. If i change it today then 6/14/2009 will change to 06/23/2009 Meaning this will be modification date. please can anybody help me with this. Appreciate your time. Thanks. |
Thread Tools | |
Display Modes | |
|
|