Application.Undo (Method 'Undo of Object' _ application failed)

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
107
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone,

I am very new to Mr.Excel message board and looking for some help with VBA since i am new to this as well. I created two events a change and a calculate as per below. the issue i am getting a method"Undo of Object'_ application failed error. the weird thing is that this error goes away if i delete / remove the calculate event ! or to be more specific if i remove the else Waterbblperbbl.ClearComments in the calculate event, then the change event code would work perfectly. i do not understand why the two events are incompatible? i need to have the two events in my sheet. Could anyone help?

VBA Code:
Private Sub Worksheet_Calculate()

Dim Waterbblperbbl As Range

Set Waterbblperbbl = Range("F22")

If Waterbblperbbl > 0 Then

Waterbblperbbl.ClearComments

Waterbblperbbl.AddComment ("bbl of water per 1 bbl of mud")

Else

Waterbblperbbl.ClearComments

End If

End sub

the second event is a change one as follow:

If Target.Address = Range("$D$15").Address Then
    Range("$F$15").ClearContents
    End If

If Target.Address = "$AJ$3" Then
    If Range("AJ3").Value <> Sheet4.Range("E7") Then
    Application.Undo
    MsgBox "This cell content cannot be changed"
    End If
    End If
 
Last edited by a moderator:
The Worksheet_Change appears to be running first.
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Even stranger thing is after setting breakpoints and stepping through the code now the Calculate event appears to run first and now produces the error.
 
Upvote 0
After playing around with it sometimes I am getting back to it running ok (just been copying and pasting the Made by Mr Bean back in, enabling Events and resetting, done it about 1/2 dozen times and then back to getting the messagebox on a run).

It isn't consistent though, I'll have some more play arounds later to see if I can narrow it down.
 
Upvote 0
The simplest option as the code is testing for a specific value is to just put that value back
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Address = "$A$1" Then
Application.EnableEvents = False
If Target.Value <> "Made by Mr Bean" Then
Target.Value = "Made by Mr Bean"
MsgBox ("Please do not change the content of this cell")
End If
End If
Application.EnableEvents = True
End Sub
 
Upvote 0
Solution
The simplest option as the code is testing for a specific value is to just put that value back
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Application.EnableEvents = False
If Target.Value <> "Made by Mr Bean" Then
Target.Value = "Made by Mr Bean"
MsgBox ("Please do not change the content of this cell")
End If
End If
Application.EnableEvents = True
End Sub

[/CODE
[QUOTE="Fluff, post: 5572613, member: 289073"]
The simplest option as the code is testing for a specific value is to just put that value back[CODE=vba]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Application.EnableEvents = False
If Target.Value <> "Made by Mr Bean" Then
Target.Value = "Made by Mr Bean"
MsgBox ("Please do not change the content of this cell")
End If
End If
Application.EnableEvents = True
End Sub

[/QUOTE]
The simplest option as the code is testing for a specific value is to just put that value back
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Application.EnableEvents = False
If Target.Value <> "Made by Mr Bean" Then
Target.Value = "Made by Mr Bean"
MsgBox ("Please do not change the content of this cell")
End If
End If
Application.EnableEvents = True
End Sub
Thank you Fluff and Mark. greatly appreciated.

i used the simplified code that Fluff wrote above and it worked perfectly. The only remaining question is why the Application.Undo method didn't work? any idea?

thank you
 
Upvote 0
The calculate event was being called before the change event & as the calculate event removes the cell comment it destroys the undo stack.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top