Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event and undo stack
Based on discussions, it's clear that running a macro via the Worksheet_Change event wipes out the undo stack. But I'm finding that there are limited things I can code that will not wipe the stack. For example, I want the macro to do it's thing only when cells within certain ranges are selected. So if I write code that just determines if the selected cells are within the range of interest, then the undo stack is left alone if no cells in the range are selected. My question is this: is there some list or information that tells me what can be executed without wiping the undo? I would imagine that anything that changes cell contents would wipe the stack. But I'm finding even calling functions that only manipulate local variables or objects causes a wipe. Any insights on this? Thanks, John P.S. here's the code in case it helps. In this code, the intersect call causes the wipe. Even without that, there is code in the FillAcctCode macro would cause it. Private Sub Worksheet_Change(ByVal Target As Range) Dim intsecCatSubcat As Range Dim currRow As Range Dim colCategory As Long Application.EnableEvents = False colCategory = Range("CatSubcatCols").Column If Target.Column + Target.Columns.Count - colCategory 0 And _ colCategory - Target.Column + 1 = 0 And _ Target.Row = Me.Range("ExpenditureTable").Cells.Row Then Set intsecCatSubcat = Application.Intersect(Target, Me.Range("CatSubcatCols")) FillAcctCode intsecCatSubcat GetUniqueAccts End If GoTo ExitThisSub ErrThisSub: ' place holder for error handling when it becomes needed. ExitThisSub: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event and undo stack
Hi Nothing changed by macro can be un-done again. Normally you would check if Target intersect with the specified cells like this: set isect=Intersect(Target, Range("B2:D10")) If not isect is nothing Then 'Target intersect with desired range 'Here goes you code End If Hopes this helps .... Per "DocBrown" skrev i meddelelsen ... Based on discussions, it's clear that running a macro via the Worksheet_Change event wipes out the undo stack. But I'm finding that there are limited things I can code that will not wipe the stack. For example, I want the macro to do it's thing only when cells within certain ranges are selected. So if I write code that just determines if the selected cells are within the range of interest, then the undo stack is left alone if no cells in the range are selected. My question is this: is there some list or information that tells me what can be executed without wiping the undo? I would imagine that anything that changes cell contents would wipe the stack. But I'm finding even calling functions that only manipulate local variables or objects causes a wipe. Any insights on this? Thanks, John P.S. here's the code in case it helps. In this code, the intersect call causes the wipe. Even without that, there is code in the FillAcctCode macro would cause it. Private Sub Worksheet_Change(ByVal Target As Range) Dim intsecCatSubcat As Range Dim currRow As Range Dim colCategory As Long Application.EnableEvents = False colCategory = Range("CatSubcatCols").Column If Target.Column + Target.Columns.Count - colCategory 0 And _ colCategory - Target.Column + 1 = 0 And _ Target.Row = Me.Range("ExpenditureTable").Cells.Row Then Set intsecCatSubcat = Application.Intersect(Target, Me.Range("CatSubcatCols")) FillAcctCode intsecCatSubcat GetUniqueAccts End If GoTo ExitThisSub ErrThisSub: ' place holder for error handling when it becomes needed. ExitThisSub: Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event and undo stack
It's actually more true that once a macro causes the undo stack to be flushed, nothing (even earlier actions that didn't call macros) can be undone. I agree that the Intersect method gets the range of cells I'm interested in. But I found that Intersect method is the first line of code that wipes the undo stack. if I manually determine the intersected range as I did, then if the selected range does not include the desired range the undo stack is left intact. I found it interesting that it's not the envocation of the macro that wipes the undo stack but functions executed in the macro that trigger the wipe. Hence my question about is it known what other functions leave the undo stack intact. John "Per Jessen" wrote: Hi Nothing changed by macro can be un-done again. Normally you would check if Target intersect with the specified cells like this: set isect=Intersect(Target, Range("B2:D10")) If not isect is nothing Then 'Target intersect with desired range 'Here goes you code End If Hopes this helps .... Per "DocBrown" skrev i meddelelsen ... Based on discussions, it's clear that running a macro via the Worksheet_Change event wipes out the undo stack. But I'm finding that there are limited things I can code that will not wipe the stack. For example, I want the macro to do it's thing only when cells within certain ranges are selected. So if I write code that just determines if the selected cells are within the range of interest, then the undo stack is left alone if no cells in the range are selected. My question is this: is there some list or information that tells me what can be executed without wiping the undo? I would imagine that anything that changes cell contents would wipe the stack. But I'm finding even calling functions that only manipulate local variables or objects causes a wipe. Any insights on this? Thanks, John P.S. here's the code in case it helps. In this code, the intersect call causes the wipe. Even without that, there is code in the FillAcctCode macro would cause it. Private Sub Worksheet_Change(ByVal Target As Range) Dim intsecCatSubcat As Range Dim currRow As Range Dim colCategory As Long Application.EnableEvents = False colCategory = Range("CatSubcatCols").Column If Target.Column + Target.Columns.Count - colCategory 0 And _ colCategory - Target.Column + 1 = 0 And _ Target.Row = Me.Range("ExpenditureTable").Cells.Row Then Set intsecCatSubcat = Application.Intersect(Target, Me.Range("CatSubcatCols")) FillAcctCode intsecCatSubcat GetUniqueAccts End If GoTo ExitThisSub ErrThisSub: ' place holder for error handling when it becomes needed. ExitThisSub: Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stack Space Problem with On Worksheet Change Event | Excel Programming | |||
Stack Space Problem with On Worksheet Change Event | Excel Programming | |||
Stack Space Problem with On Worksheet Change Event | Excel Programming | |||
Stack Space Problem with On Worksheet Change Event | Excel Programming | |||
Worksheet Change event code, but retain Undo? | Excel Programming |