Before Change Event
Is there a way to create a before change event? We want to track changes that are made to cells in a sheet. For example, if someone does a copy/paste or a Replace/Replace All, we want to know what the cell addresses are that were changed and what the before and after contents of the cell(s) are. We know how to find out what the contents are after the change, but not how to capture what they were before the change.
Before Change event, no, not really... until something is committed (via enter etc...) it does not really "exist" in the file thus the general approach is to use Application.Undo & Redo... ie trap the change occurring (via Change event), disable events, undo the action - see what the values of interest were prior to the change - store the "old" data as required - redo the change action (store new values as required) - re-enable events. However in general this approach is only really viable if you know specifically what you're looking for in terms of areas being altered etc... I wouldn't say it's a great idea to use constantly. I think you had another question along similar lines re: changing of formulae etc... sounds to be me like you might need to invest in some sort of version control software...
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you! We have actually tried using the Application.Undo. However, once that code runs, the user is then not able to press the Edit-Undo if they decide they don't want to keep the changes. Do you know if there is a way to create your own event to capture this data before the change?
I'm not sure I understand your last post I'm afraid.. can you elaborate in terms of the process you're following ?
Based on your other thread I am envisaging this being a requirement on a number of sheets and for a relatively large number of cells in which case it is my personal opinion that you will struggle to come up a truly viable solution... what you're trying to do could become very complex very quickly.
It can be done using a combination of Change & Selection events
Place code in a sheet module (not sheet3) & the following code will list selected cell address & existing value from that sheet to sheet3 column A & B
It is an example so always starts at row 1
By using a public range variable set within the SelectionChage it could be compared to that values in the Change event
Please Login or Register to view this content.
Please Read Forum Rules Before Posting
Wrap VBA code by selecting the code and clicking the # icon or Read This
How To Cross Post politely
Top Excel links for beginners to Experts
If you are pleased with a member's answer then use the Scales icon to rate it
If my reply has assistedor failed to assist you
I welcome your Feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks