How to tell What action is causing an on event Workbook change

[Steveapa]

Hi All.

I am trying to write a "change tracking" routine and log changes to a hidden tab. I have it working for individual cells changes and range changes by copy or paste.

I need to know what action excel is doing that causes the event workbook_sheetchange... A copy / Paste or an insert / delete.. etc. Then I can use that to decide what to log.

Thanks.
Steve

[gmr4evr1]

Assuming I understand your post correctly and you have the code already you can click in the code window and press F8 and it will step through each line of code every time you press F8.

[MarvinP]

Hi Steve,

I think you are asking about a Worksheet_Activate event? I use these when I have a pivot table on a sheet and want it to refresh whenever someone clicks on that tab. See:

http://www.ozgrid.com/VBA/run-macros-change.htm

If that isn't it then supply a short example or give more information.

[Steveapa]

Sorry for the poor description. Let me try again.

I want to track all changes to any sheet in a workbook. The file is a Profit and Loss Statement with many sheets. I want to know if anyone changes any value or formula on any of the sheets. So, anytime anyone changes anything in the file, I want to log the changes to a hidden tab named "Log".

I have attached a picture of what the output is from the VBA Workbook_SheetChange event

LogSheet.JPG

Here is my code in process...

Please Login or Register  to view this content.

I want to know is there anyway to determine in the Workbook_SheetChange sub, what caused the Change... Was it an Insert Row, a Delete Row?

[Steveapa]

OK.. I have been able to do my tracking of modification:
Edit a cell - I can track the modification
clear a cell - I can track the modifications

Now... If someone copies and pastes a row (or column), I know what the values are, but I don't want to loop thru 16,000 cells to see what the previous values were and then what the new values are changing to. How do I handle this?