在工作簿中使用VBA时，撤销在Excel中的更改功能

I have the following VBA code in my excel workbook:

我的excel工作簿中有以下VBA代码:

    Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 3
    If Target.Column = 1 Then
    Target.Interior.ColorIndex = 2
End If
End Sub

As I have found out, the undo function is lost. I have searched online and found this:

正如我发现的，撤销功能已经丢失。我在网上搜索发现:

Building Undo Into an Excel VBA Macro However this will only undo the changes the last VBA code performed and not any other changes that are made without the use of VBA code in my workbook e.g. undoing a copy and paste

将Undo构建到Excel VBA宏中，但是这只会撤销最后执行的VBA代码的更改，而不会撤销在我的工作簿中没有使用VBA代码的任何其他更改，例如，删除复制和粘贴

I have added this in the code but still no luck!

我已经在代码中添加了这个，但是仍然没有成功!

Application.OnUndo "Primary Macro", "UndoPrimary"

This is the message that appears on my mac which is a similar message to when I use my Windows7 PC

这是出现在我的mac上的消息，与我使用Windows7 PC时的消息类似

http://s16.postimg.org/m8hi9i4qd/error.jpg

http://s16.postimg.org/m8hi9i4qd/error.jpg

1 个解决方案

#1

Why don't you save states?

你为什么不拯救国家?

'Create global arrays to store your colors in
Dim myColors() As String
Dim myFontColors() As String

'Create a function to save your state
Private Function SaveState(ByVal Target As Range)
    Dim x, index As Long
    index = 0

    'Make your arrays the same size as the target Range
    ReDim myColors(Target.Count)
    ReDim myFontColors(Target.Count)

    For Each x In Target.Cells
        myColors(index) = x.Interior.Color
        myFontColors(index) = x.Font.Color
        index = index + 1
    Next x
End Function

'Create a function to load your state
Private Function LoadState(ByVal Target As Range)
    Dim x, index As Long
    index = 0
    'Check to make sure the arrays have data in them first
    If UBound(myColors) = 0 Or Ubound(myFontColors) = 0 Then Exit Function

    For Each x In Target.Cells
        x.Interior.Color = myColors(index)
        x.Font.Color = myFontColors(index)
        index = index + 1
    Next x
End Function

Sub TestIt()
    Dim myRange As Range

    Set myRange = ActiveSheet.Range("A1:A12")
    SaveState myRange

    'I use a different column to see what's happening
    Set myRange = ActiveSheet.Range("B1:B12")
    LoadState myRange
End Sub