| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Update Date Cell when any of target cells change | Excel Worksheet Functions | |||
| change the name of a worksheet/update VBA code | Excel Discussion (Misc queries) | |||
| Update date field upon cell range change | Excel Worksheet Functions | |||
| How to prevent file date change when no update made in Excel? | Excel Discussion (Misc queries) | |||
| How do I get one worksheet to update when others change? | Excel Worksheet Functions | |||
Linear Mode
