VBA, how to tell if a worksheet changed (xl 2003)

Topic

New Reply

I would like to update a footer on every worksheet in the workbook only if the worksheet has changed. I can tell if the workbook has changed, but I do not know how to tell if a worksheet has changed or which worksheet has changed.

The number of worksheets is dynamic.

The only thing I can think of is to create an array and update it using the worksheet_change event. However, I am hoping there is property I am not finding that simply tells me the worksheet has changed since last save.

Reply | Quote

Replies

You could use the Workbook_SheetChange event in the ThisWorkbook module of the workbook. That way you wouldn’t have to put code in each worksheet. The syntax of the event procedure is

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

where Sh is the worksheet that has been changed, and Target the range on that worksheet that has been changed.

Reply | Quote

I would like to update a footer on every worksheet in the workbook only if the worksheet has changed. I can tell if the workbook has changed, but I do not know how to tell if a worksheet has changed or which worksheet has changed.

The number of worksheets is dynamic.

The only thing I can think of is to create an array and update it using the worksheet_change event. However, I am hoping there is property I am not finding that simply tells me the worksheet has changed since last save.

Further to Hans’ guidance, you should be aware that the Workbook_SheetChange event will not catch changes to the value of a cell as a result of change to a referenced cell. On the other hand, activating the formula bar and subsequently hitting the Enter key or button will fire the Workbook_SheetChange event. This latter inconvenience can be overcome by testing to ensure that there has been a change to the cell’s formula.

Reply | Quote

The change event does not trigger when the value of a formula changes because the cell’s contents have not changed, it is still the same formula.

If one is concerned about a formula changing, one should look for changes in cells that affect the formula: the cells that are actually changing…

Steve

Reply | Quote