Oops! You are right. Use this code instead.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1").Value = Date
Application.EnableEvents = True
End Sub
The problem is when you change the value of Range A1 the Worksheet Change
Event fires again, and again, and again and again, resulting in an endless
loop which throws an error. This code will temporarly disable the event
while the data is changed.
Hope this helps! If so, click "YES" below.
--
Cheers,
Ryan
"spreadsheetlady" wrote:
> Thank-you for answering Ryan.
>
> The procedure you wrote works the way I wanted it to, but....
>
> I get a run error at the end of the procedure: " Method 'Value' of Object
> 'Range' failed."
>
> It hilites this line of code in yellow: Range("A1").Value = Date
>
> I don't understand why it's doing it. It seems to do what we want it to.
> But when it's done running, it shows the above run error.
>
> I have Excel 2007.
>
> Amy
>
> "Ryan H" wrote:
>
> > The SheetChange Event fires when you activate a different sheet within the
> > workbook. I think this is what you are wanting.
> >
> > Put this code in worksheet module.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Range("A1").Value = Date
> > End Sub
> > --
> > Cheers,
> > Ryan
> >
> >
> > "spreadsheetlady" wrote:
> >
> > > Hi,
> > > My work sheet has a date in cell: "A1".
> > >
> > > I would like the date to update (only) if there is a change in the sheet.
> > >
> > > I'm thinking of this to start:
> > > Something like Private Sub Workbook_SheetChange(By Val Sh As Object, By Val_
> > > Target As Excel.Range)
> > >
> > > Thanks in advance,
> > > Amy
> > >
> > >
> > >
|
Similar Threads
