none
Excel VBA: application.undo does not work if data is entered in a cell and the user clicks on the next cell rather than hitting Enter RRS feed

  • Question

  • I have a routine that collects the old value after a change. It is based on using the undo method to collect the old value. I am finding that if the cell is changed by hitting an 'Enter', then the Application.undo will work. If the cell is changed by clicking on another cell, the Application.Undo will not work. Here is my code thus far:

    Private Sub worksheet_change(ByVal Target As Range)
    
        Dim nextRow As Integer
        Dim oldValues As Variant
        Dim newValues As Variant
        Dim oldRev, newRev, diffRev As Double
        Dim NumRows, NumCols As Integer
        Dim lRow, lCol As Integer
    
        If Not Intersect(Target, Range("Small")) Is Nothing Then
           ' On Error GoTo err
    
            NumRows = Target.Cells.Rows.Count
            NumCols = Target.Cells.Columns.Count
    
            newValues = Target.Value2
    
            Application.EnableEvents = False
            Application.Undo
            oldValues = Target.Value2
            ...
    

    In both cases, the value in the cell does change. In both cases, the worksheet change triggers. It is just that the undo doesn't work unless an Enter is used. Thanks for any help. Rich

    Friday, February 10, 2017 9:02 PM

All replies

  • What version of Excel are you using? I have tried every which way in Excel 2010 and cannot emulate the problem. Every time I make a change, irrespective of whether I use enter or click on another cell, the undo reverts to the previous value.

    I even changed the defaults for enter key in Options and tried each option of not move, move down, up, right and left and then changing the value in a cell and clicking on another cell the undo still worked.

    Not only does the undo work but the cell where the cell that is changed is re-selected.

    Do you have a Worksheet_SelectionChange event? If so, try disabling it to see if something in that is causing the problem. If it is the problem and you can't fix then post a copy of your Worksheet_SelectionChange code. I set up some dummy code in selection change but was still not able to emulate the problem but that does not mean that you have not got some code that is a problem.


    Regards, OssieMac

    Monday, February 13, 2017 5:05 AM
  • Hi,

    I have tested your code with modifications and completion,however I find it works well both when the cell changed by hitting the “Entry” and clicking on another cell.

    Here is my code.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim nextRow As Integer
        Dim oldValues As Variant
        Dim newValues As Variant
        Dim oldRev, newRev, diffRev As Double
        Dim NumRows, NumCols As Integer
        Dim lRow, lCol As Integer
        If Not Intersect(Target, Range("A1:G10")) Is Nothing Then
            NumRows = Target.Cells.Rows.Count
            NumCols = Target.Cells.Columns.Count
            newValues = Target.Value2
            Application.EnableEvents = False
            Application.Undo
            oldValues = Target.Value2
            Target = newValues
            Application.EnableEvents = True
        End If
        MsgBox oldValues
    End Sub
    

    If it can’t help you, I suggest you to post all your code for more help.

    Monday, February 13, 2017 5:21 AM
  • Greetings.

    I am using Excel 2013.

    I can set a breakpoint at the line Application.enableevents = false

    My cell that I am changing currently contains a 3.
    Then I type in a 7  And I hit Enter.
    The code breaks at the line.
    I step one command at a time. And when I execute the Application.Undo, I can see the cell change back to 3.
    Then the rest of the program runs fine.

    Now, with the cell containing 3:
    I enter 7 and then click on the cell to the right.
    The code breaks at the same line.
    I step to and execute the Application.Undo, and the cell stays at 7.  It does NOT undo.
    Then the rest of the program runs fine.

    By the way, when I disable the Worksheet Selection Change, the above description is still the same.
    Makes no difference.

    Rich


    • Edited by RTemen Friday, February 17, 2017 2:57 PM
    Friday, February 17, 2017 2:51 PM
  • Hi, OssieMac.

    I was thinking about your suggestion of disabling the On Worksheet Selection Change.
    Since it did not matter if that routine was active or not, I started wondering if something else is interfering.

    So I made a new spreadsheet with one cell in it and put in the following code:

    Private Sub worksheet_change(ByVal Target As Range)
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
     End Sub

    This works fine with entering data and clicking on another cell.
    So, what would you suggest as a method of debugging what in my main program is 'interfering' with the Undo???

    Rich

    Friday, February 17, 2017 3:45 PM
  • Do you have any other event code in the project? If so, place a MsgBox as the first line of code in each event like the following example and see if one is getting called.

    Private Sub worksheet_change(ByVal Target As Range)

           MsgBox "Private Sub worksheet_change called"

            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
     End Sub

    Also, post all of the code that you have in your sub and I will test further. Tell me what the actual range is for Range("Small") so I can create a defined name for the range. 


    Regards, OssieMac

    Friday, February 17, 2017 7:45 PM
  • I was doing some testing and investigating. I was setting up the same scenario on another sheet so that I could try to determine what was interfering with the undo.

    So far, I have not been able to make the undo fail.
    We are currently building a copy of our failing sheet on a new sheet, one step (feature) at a time to see where it breaks.

    So far, we are almost done duplicating the original sheet and it is not failing, yet.

    I'll let you know what we find.

    Rich

    Friday, February 17, 2017 8:27 PM