Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    EXCEL: track cell delete (Excel2003)

    I need to find out what the cell-contents was of a deleted cell in column A. The start code is easy; I trap the event.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    '
    If Target.Column = "1" Then

    End If
    '
    Application.EnableEvents = True
    End Sub

    However, I need to take action based on the value that was in the cell BEFORE the content was changed (or deleted). The current content can remain, as long as I know what was there before. Should I play with "undo" & 're-do' here somehow?

    Thanks,

    Erik Jan

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: EXCEL: track cell delete (Excel2003)

    It might be easier to keep track of the value of all the cells you care about in an array. You could populate it when the workbook is opened and update it with your Worksheet_Change event.

    StuartR

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: EXCEL: track cell delete (Excel2003)

    What do you want to do if multiple cells are deleted at once and some or all of them are in column A?
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: EXCEL: track cell delete (Excel2003)

    The Application object has an Undo method, but no Redo method. So you'd have to store the current (i.e. new) value or formula in a variable, undo the change, and if desired restore the value or formula from the variable.

    However, the user could change multiple cells in one go, for example by selecting a range and pressing Delete. So you'd have to store and restore an array of previous values/formulas. This could become quite tricky. And if the user selects Edit | Clear | All, you have an additional problem since Undo also applies to cell formatting.

    The attached workbook shows a slightly different approach.
    I have assumed that column A will only contain values, not formulas, and that you're only interested in the first 1000 rows.
    When the workbook is opened, an array is filled with the values of Sheet1!A1:A1000.
    The Worksheet_Change event of Sheet1 compares the new value of the changed cells in A1:A1000 with the value stored in the array, does something with it (in this demo, it dislays a message box), then updates the array.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL: track cell delete (Excel2003)

    Very cool, I think this is what I need, will tweak to fit my code.

    Thanks <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL: track cell delete (Excel2003)

    Hi... here I am again, two years later with the same question. This time I'd need more protection than the workaround provided in the 2007 contributions in this thread. I want to dynamically protect my current application against any edits in certain marked rows and outside a pre-set range.
    The rows do not have to be adjacent, the range is variable. I'd like to protect against everything: selecting one or more cells, deleting values and/or formatting.

    Two years later and two years wiser (?); any new ideas and/or suggestions as to what might be done here?? I guess it's strange to know (by experience) that virtually everything is possible in Excel and certainly with the use of VBA. Why would preventing cell-entries based on certain conditions be impossible??

    O... and I know I can protect the sheet and ranges but I guess this is not flexible enough. What would be the problem if I'd use an Application.Undo in my Worksheet.Change event if I don't want the entry to stick? What wouldn't work anymore then???

    Any advise or additional suggestions or even wild ideas are appreciated <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

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

    Re: EXCEL: track cell delete (Excel2003)

    Why is protecting the sheet (after unlocking the appropriate ranges) not flexivle enough?

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL: track cell delete (Excel2003)

    It's sort of an ordering sheet... there will be lines (rows) for each item to be ordered. Once the order is placed, the line should be 'protected'. So e.g. lines 3, 8 and 9 could have been ordered and no changes should be accepted. The person who does the ordering however should have access to all lines.
    Then, for everybody, it should only be allowed to change to lines that have entries (and are not yet ordered); so no one should be able to make changes 'somewhere' on the sheet (e.g. in the headers) even better, only the owners of an entry (I know who is logged on!) should be allowed to changes their own entries.
    Sounds complex but it is logical (I think...), makes sense?

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

    Re: EXCEL: track cell delete (Excel2003)

    That sounds like a task for a database to me...

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL: track cell delete (Excel2003)

    I'd change the locked property of the lines in question and protect the sheet.
    Or port this to a database...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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