Undo "undo"?

[The Cube]

Hi all
Is there a VBA instruction to reverse the effect of Application.Undo?

I wish to achieve the following effect:
If I change any values in Worksheet("Codes").Range("Short_name_range")
then those changes must be reflected by identical changes in
Worksheet("Analysis").Range("Account_1_Range") and in
Worksheet("Analysis").Range("Account_2_Range") wherever there is a match
between the original values. For various reasons I cannot link these cells
by formulae and I therefore have to make the changes by an event handler.

Code follows, followed by the observed problem when this triggers in
practice:

Private Sub Worksheet_Change(ByVal Target As Range) 'Worksheet("Codes")
Dim lRowCount As Long
Dim lRow As Long
Dim lTestRow As Long
Dim rUndoValues As Range
Dim rShortNameRange As Range
Dim rUndoSelection As Range
Set prSelection = Intersect(Target, Range("short_name_range")) 'Public
If prSelection Is Nothing Then Exit Sub
pbMeWriting = True 'Public
lRowCount = prSelection.Rows.Count
With Application
.EnableEvents = False
.Undo
With .ThisWorkbook.Worksheets("Codes")
Set rShortNameRange = .Range("short_Name_Range")
Set rUndoValues = .Range("UndoValues_Range")
Set rUndoSelection = prSelection.Offset(.Range("UndoRow"), _
.Range("UndoColumn"))
rShortNameRange.Copy
rUndoValues.PasteSpecial Paste:=xlPasteValues
End With '.ThisWorkbook.Worksheets("Codes")
.CutCopyMode = False
.EnableEvents = True
With .ThisWorkbook.Worksheets("Analysis")
For lRow = 1 To lRowCount
For lTestRow = 1 To .Range("Depth_Range").Value
If rUndoSelection.Rows(lRow).Value <> _
.Range("Account_1_Range").Rows(lTestRow).Value Then GoTo Test2
.Range("Account_1_Range").Rows(lTestRow).Value = _
prSelection.Rows(lRow).Value
Test2:
If rUndoSelection.Rows(lRow).Value <>
.Range("Account_2_Range").Rows(lTestRow).Value Then GoTo NextTestRow
.Range("Account_2_Range").Rows(lTestRow).Value = _
prSelection.Rows(lRow).Value
NextTestRow:
Next lTestRow
Next lRow
End With '.ThisWorkbook.Worksheets("Analysis")
End With 'Application
End Sub 'Worksheet_Change(ByVal Target As Range)


PROBLEM:
The effect of Application.Undo appears to be permanent. Somehow I need to
store the changes before they are undone, so that the changes made to the
worksheet "Codes" ultimately take effect. But any attempt to store these
values by copy/paste before Application.Undo causes Undo to crash with error
1004 and I then have to "Clean" the project before the event handler will
kick in at all.

Thanks

-Cube

 

[Don Guillett]

I didn't take a look at this but can't you store and then move back without
the undo?

 

[The Cube]

I didn't take a look at this but can't you store and then move back without
the undo?

That was quick, Don, thanks for the response.
Unfortunately I did not quite understand it. Can you rephrase?
I need to be able to access both the details immediately prior to the
change, and the details immediately subsequent to the change, with the
worksheet ultimately left as immediately subsequent to the change.
To get the details immediately prior to the change I used Application.Undo,
but this lost me the details subsequent to the change.
And storing the details subsequent to the change prior to Application.Undo
crashes the undo command (this is consistent with the online help on
Application .Undo that requires it to be the first instruction).
If there is an alternative approach I would be pleased to know it, so how,
as you say, do I "move back without the undo"?

Thanks
-Cube

 

[J.E. McGimpsey]

A somewhat different approach:

Store the values in "Short_name_range" in a public variant variable.
When a change occurs within Short_name_range, compare the Analysis
sheet's values to the variant. If it's the same, change it to the
new value. No undo's required:

in the Worksheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim i As Long
With Range("short_name_range")
If Intersect(Target, .Cells) Is Nothing Then Exit Sub
For i = 1 To .Rows.Count
Application.EnableEvents = False
If .Cells(i).Text <> pvShortNames(i, 1) Then
For Each cell In _
Sheets("Analysis").Range("Account_1_Range")
If cell.Value = pvShortNames(i, 1) Then _
cell.Value = .Cells(i).Value
Next cell
For Each cell In _
Sheets("Analysis").Range("Account_2_Range")
If cell.Value = pvShortNames(i, 1) Then _
cell.Value = .Cells(i).Value
Next cell
pvShortNames(i, 1) = .Cells(i).Value
Application.EnableEvents = True
End If
Next i
End With
End Sub

in a regular code module:

Public pvShortNames As Variant

To initialize the variable, in the ThisWorkbook module:

Private Sub Workbook_Open()
pvShortNames = _
Sheets("Codes").Range("short_name_range").Value
End Sub

 

[Don Guillett]

See J.E.'s post

That was quick, Don, thanks for the response.
Unfortunately I did not quite understand it. Can you rephrase?
I need to be able to access both the details immediately prior to the
change, and the details immediately subsequent to the change, with the
worksheet ultimately left as immediately subsequent to the change.
To get the details immediately prior to the change I used Application.Undo,
but this lost me the details subsequent to the change.
And storing the details subsequent to the change prior to Application.Undo
crashes the undo command (this is consistent with the online help on
Application .Undo that requires it to be the first instruction).
If there is an alternative approach I would be pleased to know it, so how,
as you say, do I "move back without the undo"?

Thanks
-Cube

 

[The Cube]

Thanks JEMcG

-Cube

A somewhat different approach:

<snip>

 

[Tom Ogilvy]

You don't have to clean the project to enable events. Since you turned them
off, go to the immediate window and do

Application.EnableEvents = True <cr>

 

[The Cube]

You don't have to clean the project to enable events. Since you turned them
off, go to the immediate window and do

Application.EnableEvents = True <cr>

I agree with the theory, Tom, but in practice this workbook falls down. I
know how to get around it now, but I can consistently reproduce the effect
where running the macro dirties the workbook. I set enableevents = true in
the Workbook_Open() code, but saving, closing and reopening the workbook I
find that the event handler remains disabled until I run Rob Bovey's code
cleaner.

Don't worry about it, I am sure that the fault lies elsewhere in the
workbook.

Thanks for the time.

-Cube