I'm trying to make UNDO work with a conditional-formatting VBA routine. I've written a Worksheet_Change routine and I've figured out how to undo the formatting that my own code creates. But after that I don't know how to undo the change that triggered the Worksheet_Change Event. Excel's pre-existing Undo stack has been cleared, so it's impossible to back-up any further. Can anyone help? Or is what I'm trying to do impossible without completely reimplementing Excel's Undo functionality myself?
In case it helps, here's my code. In a module, some global variables and the undo formatting routine:
'Custom data type for undoing
Type SaveRange
RBorderWeight As Long
RBorderColor As Variant
Addr As String
End Type
'Stores info for undo
Public OldWorkbook As Workbook
Public OldSheet As Worksheet
Public OldSelection() As SaveRange
Sub UndoFormat()
prevEnableEvents = Application.EnableEvents
prevScreenUpdating = Application.ScreenUpdating
Application.EnableEvents = False
Application.ScreenUpdating = False
OldWorkbook.Activate
OldSheet.Activate
For i = 1 To UBound(OldSelection)
Range(OldSelection(i).Addr).Borders(xlEdgeRight).Weight = OldSelection(i).RBorderWeight
Range(OldSelection(i).Addr).Borders(xlEdgeRight).ColorIndex = OldSelection(i).RBorderColor
Next i
Application.ScreenUpdating = prevEnableEvents
Application.EnableEvents = prevScreenUpdating
End Sub
In the Worksheet code, the conditional formatting routine:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iArea As Range 'for looping thru Areas
Dim iCell As Range 'for looping thru Cells
Dim Focus As Range 'intersection of Target & pre-chosen named range
ReDim OldSelection(1 To Target.Cells.Count) 'global variable stores undo info
Dim i As Long 'index for undo array
Application.EnableEvents = False
Application.ScreenUpdating = False
'store undo information
Set OldWorkbook = ActiveWorkbook
Set OldSheet = ActiveSheet
i = 0
For Each iCell In Target
i = i + 1
OldSelection(i).Addr = iCell.Address
OldSelection(i).RBorderWeight = iCell.Borders(xlEdgeRight).Weight
OldSelection(i).RBorderColor = iCell.Borders(xlEdgeRight).ColorIndex
Next iCell
'change formatting of Target
For Each iArea In Target.Areas
Set Focus = Intersect(iArea, ThisWorkbook.Names("ledger_data").RefersToRange)
If Focus Is Nothing Then
'do nothing
Else
For Each iCell In Focus.Cells
If iCell.Column = 4 Then
iCell.Borders(xlEdgeRight).Weight = xlThin
iCell.Borders(xlEdgeRight).ColorIndex = 4
ElseIf iCell.Column > 1 And iCell.Column < 10 Then
iCell.Borders(xlEdgeRight).Weight = xlThin
iCell.Borders(xlEdgeRight).ColorIndex = 5
End If
Next iCell
End If
Next iArea
Application.OnUndo "Undo border formatting", "UndoFormat"
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Also, I'm already using Excel's built-in conditional formatting for something else in the same range, so that's not a solution.
Bookmarks