Record update date in cell

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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?