Record update date in cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Cell AV17 holds data that is periodically updated. In cell AV18 I need a
formula to have the date of the AV17 update automatically inserted. What
would the formula be?
 
Hi Patrick,

I put the code in the worksheet VBA, and received the following compile
error: "Ambiguous name detected" Worksheet_Change." The first line of code
is highlighted yellow. Where did I go wrong?

Thanks, Phil
 
You can only have one change event in a sheet module. sounds like you
already have one there, so you would have to modify that to also perform
this additional action.
 
Hi Tom,

You are exactly right - there are two in this worksheet code page , below.
Could you tell me how to combine the two?

Thanks, Phil

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Errorhandler
If Target.Address = "$M$7" Then
Application.EnableEvents = False
Worksheets("Strategy Map").TextBox1.Value = Target.Value
End If
Errorhandler:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AV$17" Then
Target.Offset(0, 1) = Format$(Now, "dd/mm/yy")
End If
End Sub
 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Errorhandler
If Target.Address = "$M$7" Then
Application.EnableEvents = False
Worksheets("Strategy Map").TextBox1.Value = Target.Value
End If
If Target.Address = "$AV$17" Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format$(Now, "dd/mm/yy")
End If
Errorhandler:
Application.EnableEvents = True
End Sub
 
Hi Tom,

This works exactly as needed. I think Excel is selecting the cell AZ17 to
put the date answer (for the second part of the sub). Can you make the code
place the date answer in cell AV18?

Thanks, Phil
 
I would have said AW17, but this should do AV18

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Errorhandler
If Target.Address = "$M$7" Then
Application.EnableEvents = False
Worksheets("Strategy Map").TextBox1.Value = Target.Value
End If
If Target.Address = "$AV$17" Then
Application.EnableEvents = False
Target.Offset(1, 0) = Format$(Now, "dd/mm/yy")
End If
Errorhandler:
Application.EnableEvents = True
End Sub
 
Tom,

I'm sorry about this, but I don't know what you mean. Here is what's
hapening (these are merged cells):

AR17 AV17 AZ17
Actual 90% 6/06/05 (AV17 is the updated cell. AZ17 is now
autopopulated)

AR18 AV18 AZ18
Update

What I need:

AR17 AV17 AZ17 (AV17 is the updated cell)
Actual 90%

AR18 AV18 AZ18
Update 6/06/05 (AV18 should autopopulate with the date, not
AZ17)

Thanks, Phil
 
Guess you didn't try the revised code, and this is the first mention of
merged cells. Nonetheless, let's be more specific

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Errorhandler
If Target.Address = "$M$7" Then
Application.EnableEvents = False
Worksheets("Strategy Map").TextBox1.Value = Target.Value
End If
If Target.Address = "$AV$17" Then
Application.EnableEvents = False
Range("AV18").Value = Format$(Now, "dd/mm/yy")
End If
Errorhandler:
Application.EnableEvents = True
End Sub
 
Hi Tom,

How do I use the code to work for the range of cells. I have 16 page long
data to update the date when some cells are changed.

Example:

from
C8 D8 E8 .... T8
X

to
C8 D8 E8 .... T8
X X X 7/7/05

And same down the row up to like row 500 or so..

Thanks for your help
 
Here is my porblem I have taken a job overseas in Nigeria and I have been
confronted with a worksheet that has over 19000 lines and I am converting
that into an Access data base.No problem there. The problem is the forwarding
company sends us info thru this spreadsheet and does not tell us what lines
they have added or updated. It is extremely hard for us to compare that many
lines as they send this report once a month. I want to give them a formula
that each time they change the row in any way that there is an automatic date
inserted on a column. Is this possible.
 
Did you post this in 2005 or 2006? The date says 2006, so I will assume an
answer might still be relevent.

Establish a unique index in your Access table that so that you can import
the information from the Excel file without creating duplicate records, but
can add the updated rows.

Then, run a "find duplicates" query on the Access table using criteria that
remains the same between old and updated records (for example, row number,
product type, etc). This will produce a table that contains the old and
updated records. Peruse and delete at your leisure.

If you want to update the Access table with only the new information, I
believe you can use an update query.

-Chris
 
I applied this code to record an update date in a cell and it worked
beautifully:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AV$17" Then
Target.Offset(0, 1) = Format$(Now, "dd/mm/yy hh:mm")
End If
End Sub

However, I am SUCH a novice...I do not know how to apply this code to a
range of cells. What do I add to this code to apply it to an entire column
in my worksheet?
Thanks
 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("AV1:AV1000")) 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

"AV1:AV1000" could be written as "AV:AV" for an entire column.


Gord Dibben MS Excel MVP
 
Back
Top