Lost undo, can't undo

[drhatmrexcel]

I have 10 sheets (tabs) in a workbook which are named as follows:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
HELP - Read this
Historical
Payees
ExpenseCategory
Budget
Expense Entry
App
Current App - Pivot Table
All Apps - Pivot Table
Budget Tracker
<o> </o>
4 of the sheets have an embedded macro that is causing loss of the “undo” function on those 4 sheet (tabs).
The embedded macro watches for a cell change, and if detected, then the two pivot tables on the separate pivot table sheets will be refreshed. The 4 sheets with the embedded macros are ExpenseCategory, Budget, Expense Entry, and App.
<o> </o>
The embedded macro is in the Worksheet section of the Visual Basic editor, and in the SelectionChange portion.
<o> </o>
Here is the macro that is giving me the headache.
<o> </o>
<o> </o>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Sheets("Current App - Pivot Table").PivotTables("PivotTable1").PivotCache.Refresh
Sheets("All Apps - Pivot Table").PivotTables("PivotTable2").PivotCache.Refresh
Application.ScreenUpdating = True
End Sub
<o> </o>
<o> </o>
<o> </o>
<o> </o>
Any ideas on why I am losing the “undo” function or how I can change the macro and regain the “undo” function?

 

[jbeaucaire]

Use of worksheet_change macros cause the undo stack to be cleared when they trigger. It's a side effect you have to live with when you employ these event macros.

The only workaround would be to rewrite the macros as regular macros and activate them manually, or via button, instead of letting them trigger themselves via event-code.