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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the cell with the
actual date/time. If yuo use a formula, then that will change each time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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?

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.

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the cell with the
actual date/time. If yuo use a formula, then that will change each time
the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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 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

"Phil Hageman" wrote:

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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the cell with the
actual date/time. If yuo use a formula, then that will change each time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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?

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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

"Phil Hageman" wrote:

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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the cell with
the
actual date/time. If yuo use a formula, then that will change each
time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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 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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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

"Phil Hageman" wrote:

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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the cell with
the
actual date/time. If yuo use a formula, then that will change each
time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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?

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

--
Regards,
Tom Ogilvy



"Phil Hageman" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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

"Phil Hageman" wrote:

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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the cell
with
the
actual date/time. If yuo use a formula, then that will change each
time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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?

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Phil Hageman" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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

"Phil Hageman" wrote:

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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the cell
with
the
actual date/time. If yuo use a formula, then that will change each
time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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?

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Phil Hageman" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in
message
...
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

"Phil Hageman" wrote:

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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the
cell
with
the
actual date/time. If yuo use a formula, then that will change
each
time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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?

Tom,

Sorry for the confusion - working exactly as needed.

Phil

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Phil Hageman" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in
message
...
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

"Phil Hageman" wrote:

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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the
cell
with
the
actual date/time. If yuo use a formula, then that will change
each
time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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 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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Phil Hageman" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in
message
...
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

"Phil Hageman" wrote:

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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the
cell
with
the
actual date/time. If yuo use a formula, then that will change
each
time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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?

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.

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the cell with the
actual date/time. If yuo use a formula, then that will change each time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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?

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

"Alan Johnson" wrote:

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.

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the cell with the
actual date/time. If yuo use a formula, then that will change each time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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?

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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the cell with the
actual date/time. If yuo use a formula, then that will change each time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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?

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

On Thu, 18 Jun 2009 08:57:04 -0700, cashnic
wrote:

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

"Patrick Molloy" wrote:

you need to used th esheet's changed event to populate the cell with the
actual date/time. If yuo use a formula, then that will change each time the
sheet recalculates.
right click the sheet tab & select the code page. Add this...


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



"Phil Hageman" wrote:

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?