Date does not update until worksheet change
 

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

Date does not update until worksheet change
 

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

Date does not update until worksheet change
 

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

Date does not update until worksheet change
 

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

Date does not update until worksheet change
 

"Ryan H" wrote:

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

Date does not update until worksheet change
 

Thanks Ryan. That fixed it. The routine runs great.
Amy

"Ryan H" wrote:

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