Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Cell Contents (2002)

    Thanks to Steve and John over in the Excel board for help on my "Last Edit" post, but now...

    What's the best place (event) to put code that updates a cells contents whenever a user
    makes a change to any cell?

    I want this code to update as soon as there's a change:

    cells(2,5) =ActiveWorkbook.BuiltinDocumentProperties("Last author")

    I'm thinking Workbook_SheetChange ? Is that correct?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Cell Contents (2002)

    You would use the Workbook_SheetChange event, yes. But is it really necessary? It's not as if the author is going to change with every edit of a cell, is it? It's a lot of overhead...

    When using the Workbook_SheetChange event or Worksheet_Change event, it may be wise to put Application.EnableEvents = False at the beginning of the event handler, and Application.EnableEvents = True at the end. This means that events will be ignored while the event procedure is being executed.

    For example, if you want to display the address of the most recently modified cell in A1, you could put Cells(1,1) = Target.Address in one of these events. But this will change the contents of cell A1, causing the event handler to be called again, this time with A1 as target. So cell A1 would always display $A$1. To get around it, disable events temporarily:

    Application.EnableEvents = False
    Cells(1, 1) = Target.Address
    Application.EnableEvents = True

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Update Cell Contents (2002)

    Hi Kevin,

    Working on the premise that whoever's got the workbook open would know who they are, and that they only need to know who was the last person to have changed the workbook when it was last closed, you could quite easily get by with putting the code in an auto_open macro.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Cell Contents (2002)

    Kevin,
    Closely related to macropod's suggestion is writing the UserName to your cell through an Auto_Close macro or using the ThisWorkbook ==> Workbook_BeforeClose event. However, both of these suggestions as well as macropod's suggestion of Auto_Open only show the Last User Who OPENED the file. In other words, if the last person who opened the file made NO changes, he/she would still get their UserName posted. Actually, the same holds true for the _SheetChange event. Most of these events, are fired by some "navigational" event rather than an edit or entry of data.
    If this is what you really need, the SheetCalculate event is the only thing I know of that fires every time data is entered or edited.

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Cell Contents (2002)

    Thanks, all these ideas are valuable. But I hope I can
    keep the lid on the can of worms here...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Posting Permissions

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