Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Cecil' post='788810' date='13-Aug-2009 11:39']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.[/quote]

    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.
    Regards
    Don

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •