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]

Narrowed down the problem and found the culprit (but why it is a culprit is still eluding me though!)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The error was traced to a function that was getting called when the original worksheet was getting refreshed as a natural part of Excel's screen update.<o></o>
<o></o>
I was using a custom function to get the week number from the date (since it is not available in 2003) and that function was called as a part of formula in one of the worksheets. For simplicity sake, I moved that function to my test workbook and used it in one of the cells in the same workbook, the problem got simulated! <o></o>
<o></o>
The custom funtion is <o></o>
<o></o>

[COLOR=black][FONT=Verdana]Function MyVBAWeekNum(InDate As Date, DayOfWkStart As Integer) As Integer[/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]  MyVBAWeekNum = CInt(Format(InDate, "ww", DayOfWkStart))<o:p></o:p>[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Function<o:p></o:p>[/FONT][/COLOR]

To simulate the problem:<o></o>

1. In the worksheet you are testing the insert, at say, A5 enter today's date (I entered it as "02-Apr-2010" <o></o>
2. In A6, enter "=MyVBAWeekNum ($A$5, 2)<o></o>
3. As was being tested earlier, insert row at Row#1, the row stays! If we undo manually, it goes.<o></o>

<o></o>
On debug, I find that after finishing the worksheet_change event execution, MyVBAWeekNum gets called while the screen update happens and then, this problem occurs.<o></o>
<o></o>
Here is another interesting thing - If I do not refer $A$5 in A6 and instead use "=MyVBAWeekNum("02-Apr-2010", 2)", then the problem doesn't occur. Does this give any clue? When some reference is used in a formula (no matter what the formula is), this problem seems to get kicked in!<o></o>
<o></o>
<o></o>
-sat<o></o>

 

[Richard Schollar]

Does it still happen if you have Calculation turned to manual?

 

[xlsat]

If the Calculation is set to Manual, the problem doesn't occur. The Undo works as expected.

-sat

 

[xlsat]

Just to re-iterate - Though it works when the calculation is in Manual mode, my requirement is such that I cannot afford to have it manual

Is this tending towards a bug?

-sat

 

[xlsat]

Implemented a work around to circumvent the issue.

Before the Undo code, I am turning the calculation mode to Manual and setting it back to the original after the Undo operation.

This seems to do the trick in all worksheets where I want this, except one worksheet in which the worksheet_change event itself is not being called on Insert row operation, though for other data changes, etc the change event is getting triggered! It means it is not probably the issue of EnableEvents being off. (haven't yet found the cause for this new issue). Just wanted to update on the core problem.

Thanks Richard & Snowblizz.

-sat