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

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("B210"))
If not isect is nothing Then
'Target intersect with desired range
'Here goes you code
End If

Hopes this helps
....
Per

"DocBrown" <(E-Mail Removed)> skrev i meddelelsen
news5D94B36-A4CB-4C99-9E6F-(E-Mail Removed)...
> 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
>
>

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("B210"))
> If not isect is nothing Then
> 'Target intersect with desired range
> 'Here goes you code
> End If
>
> Hopes this helps
> ....
> Per
>
> "DocBrown" <(E-Mail Removed)> skrev i meddelelsen
> news5D94B36-A4CB-4C99-9E6F-(E-Mail Removed)...
> > 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
> >
> >
>
>