I am trying to setup an audit trail for changes to any spreadsheet in the workbook. Capturing changes and posting them to an AuditLog worksheet is easy, but now I'm trying to capture any deleted rows too. That is a BIT more challenging.
To do this, I am copying the current row(s) any time the selection changes on a worksheet, to a worksheet called AuditTemp. That way, if something is then deleted, I can copy that entire row to the AuditTrail worksheet. The code I use to do this is below. It is called from the Workbook_SheetSelectionChange event. The actual code to write the changes to AuditLog is called from the Workbook_SheetChange event.
So far, if an entire row is selected (clicking on the row number to the left of the data) everything works fine. Even if multiple rows are selected, it works fine and the data on those rows are copied to the AuditTemp worksheet (with column headings as comments in each cell).
The problem is, if I right click on the row number (to select DELETE for example) the right-click menu pops up but then goes away before I can do anything. I tracked it down (by putting Exit Sub in front of different statements below) to the Copy method (rngCopy.Copy rngDest) in the code below. If that statement is not executed, the right-click menu is fine. I confirmed that by then commenting out that one line and the menu was fine.
Any ideas why this happens or how to get around it?
The other interesting thing about this is if I right-click on the row (in the data area, not on the row header) the right-click menu is fine so there is a work-around, but I don't understand the problem and don't like to have to use it that way.
Bookmarks