Application.Undo not working properly!!!

[vini1508]

i have form for change case.. it contains RefEdit control , option button, command button....
1. by using RefEdit control I'm selecting Range..
2. Option button is for selecting Upper Case..
3. On click on command button the selected range value is change to Upper Case..

this is working fine...
but now i wanted to undo the action done by userform so i have used Application.Undo.. undo button is getting activated but it is not undoing the action plss help.. code I'm using is below...

Private Type SaveRange
    Val As Variant
    Addr As String
End Type
Public OldWorkbook As Workbook
Public OldSheet As Worksheet
Private OldSelection() As SaveRange


Dim b, c
Dim i, j, k As Long


Private Sub btn_change_Click()
    Set b = ActiveSheet.Range(ref_range.Text)
    ReDim OldSelection(b.Cells.Count)
    Set OldWorkbook = ActiveWorkbook
    Set OldSheet = ActiveSheet
    j = 1
    If Application.CountBlank(b) = b.Cells.Count Then
        MsgBox "Empty Range"
        ref_range.SetFocus
    Else
        If opt_upper Then
            For Each c In b
                If c.Value <> "" Then
                    c.Value = UCase(c.Value)
                    OldSelection(j).Addr = c.Address
                    OldSelection(j).Val = c.Formula
                    j = j + 1
                End If
            Next
        End If
        ref_range.SetFocus
    End If
    Application.OnUndo "Undo the Change Case", "UndoCase"
End Sub

Sub UndoCase()
    On Error GoTo Problem
    Application.ScreenUpdating = False
    OldWorkbook.Activate
    OldSheet.Activate
    For k = 1 To UBound(OldSelection)
        Range(OldSelection(k).Addr).Formula = OldSelection(k).Val
    Next k
    Exit Sub


Problem:
    MsgBox "Can't undo"
End Sub

Whenever i click Ctrl+Z it gives Msgbox("Can't undo") everytime... pls help

 

[jtakw]

Hi,

You cannot UNDO actions performed by a macro/code, you can only UNDO actions performed in the normal Excel UI, you can also program a macro to UNDO actions performed by the normal Excel UI; nonetheless, actions performed by a macro/code CANNOT be Undone...

As already answered here at your other post: http://www.mrexcel.com/forum/excel-questions/919790-undo-actions-performed-addin.html

 

[Macropod]

Vini1508: Please read our policy on creating what are, in effect, duplicate threads in rule #12: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html