Application.Undo - Doesn't seem to be working in 2003

[xlsat]

Hello,

I have a seemingly strange problem. It seems like Application.Undo doesn't have any impact when I run my code in Excel 2003 (while in Excel 2007, it runs fine).
What I want to do: I want to avoid the user of my worksheet to shift the header row from its row 1 position. So, the user should not be able to insert any rows above the header. If he/she does, I want to undo that operation. For this, I am using a named cell in the header row and checking if it has got shifted after any change in the worksheet. My code is on these lines -

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target Is Nothing Then
        Exit Sub
    End If

    If Target.Cells.Count > 1 Then
        AvoidHeaderShifting Target
    End If
    
End Sub

Private Sub AvoidHeaderShifting (ByVal Target As Range)
    Dim iRow As Long

    HEADER_ROW_POSITION = 1
    
    iRow = Sheets("Wk1").Range("my_header_name").row
    If iRow > HEADER_ROW_POSITION Then  'if iRow is > 1, it means that the header row has got shifted
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        MsgBox "Header cannot be moved please..."
    End If

End Sub

While this works peacefully in Excel 2007, in Excel 2003, the undo has no impact! Though the code gets parsed when I insert a row above the header row, there is no undo action seen. In fact, after this runs, when the cursor is back to Excel workbook, when I do undo manually, the row insert gets undone!

Note: I did see that some posts said that the Undo should be the first statement in the macro. My understanding is that it is true if we are using Application.OnUndo to tag the macro to the undo operation done from the UI.

Any inputs towards solving this would be highly appreciated.

-sat

 

[xlsat]

Just to add to the above -

By further testing what I found is that Application.Undo doesn't seem to work for row-insert and column-insert! It worked for row-delete, column-delete, changing the data, etc... (Note for clarity - I had removed the row-shift condition check to test different scenarios).

So.... is this a bug in Excel 2003? It seems more & more because of the above and the fact that it works in Excel 2007!

Any hacks to overcome this?

-sat

 

[Richard Schollar]

Hi

it works for me in xl2002 using this code (range named Header being A1):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngRow As Long
On Error Resume Next
lngRow = Range("HEader").Row
On Error GoTo 0
If lngRow <> 1 Then
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
End If
End Sub

Works with both inserts and deletes.

 

[xlsat]

Hi Richard,

Thank you. Tried your code in my version of Excel (xl 2003 SP2).

It works for delete but not for insert again

I found a few other peculiar stuff as well - Many of the statements do not actually get "EXECUTED" though they get parsed while executing, if the worksheet_change event is called on Insert (Cell/Row/Column). For example, I just replaced .Undo with
Worksheets("Sheet3").Activate
from your code. Sheet3 gets activated for all other operations that I tried (removing the If statement for the sake of testing the event), but does not get activated when I try Insert cell/row/column! Here is the toned-down code.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
 
    MsgBox (" in change event")
    With Application
        .EnableEvents = False
        Worksheets("Sheet3").Activate
        .EnableEvents = True
    End With
End Sub

When this event gets called due to insertion, MsgBox comes up BUT, Sheet3 does not get activated!!!

 

[Richard Schollar]

I don't have xl2003 in front of me to test, but I can when i get home. I wonder if it's an update that has been applied to Excel that is causing this problem (in which case maybe there is a fix). Let me find out if it happens to my xl2003 and I'll get back to you.

 

[xlsat]

Thanks Richard. Would await your update.

-sat

 

[snowblizz]

Out of curiosity I tested this in xl2003 and I'm getting some of the behaviour you describe.
If I run this on Sheet2 it works as expected. However I can' make it Undo even though the "real button" works.

Private Sub Worksheet_Change(ByVal Target As Range)
    'On Error Resume Next
    MsgBox (" in change event")
        With Application
        .EnableEvents = False
        Worksheets("Sheet3").Activate
        .EnableEvents = True
    End With
End Sub

At one point this refused to work as well. Then I noticed that the On Error suppresses errors so the Application.enableevents remained "false".

If you run "msgbox application.EnableEvents" you'll get a true/false with your current status.
I don't recommend the "on error resume next" when developing as it hides errors.

I think what you need ot do is something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
        With Application
        .EnableEvents = False
Call undoMethod   
        .EnableEvents = True
    End With
End Sub

And make a separate sub in a module with just the Undo.

Sub undoMethod()
        With Application
        .Undo
    End With
End Sub

 

[xlsat]

@snowblizz: I tried your code - it worked! And... to my surprise even the earlier one worked, though I didnt make changes specific changes to it! The only difference b/w now and my earlier tries was that I had restarted Excel!

And, a while later, this is what happened. It just stopped working suddenly. After some analysis, it turned out that when I open another workbook that I have which runs some macros when it opens, this Undo stops working!!! In fact that is the workbook in which I wanted this functionality!!!

Whenver I open Excel and then try the test workbook, it works until I open my original workbook. What could this be due to??? Based on the answer to this, I can debug my workbook_open code.

-sat

 

[Richard Schollar]

Do you have the

Application.EnableEvents = False

in this other workbook that may be turning event code off? You should notice this because it should be obvious eg as Worksheet_Change stops working completely.

 

[xlsat]

The interesting thing is that the worksheet_change event gets called and EVEN the .Undo code gets executed, but actual undo doesn't happen!

The following combinations I tried with the original file & test file -

1. Open only test file. Result: Undo works as expected
2. Open both files (order of opening doesn't matter): change event is called, but Undo doesn't happen (if message box is there, that comes up)
3. Disable macros in original file & open both files: Undo works as expected
4. Comment out workbook_open itself, enable macros in original file and open test file : change event is called, but Undo doesn't happen. This was a surprise - I thought it will behave like macros are disabled!!!
I will try removing modules too one by one and test out!

-sat