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

nmounir

New Member
Joined
Oct 16, 2020
Messages
16
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:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
The Worksheet_Change appears to be running first.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,255
Office Version
  1. 365
Platform
  1. Windows
Very odd indeed.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,255
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 
Solution

nmounir

New Member
Joined
Oct 16, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,255
Office Version
  1. 365
Platform
  1. Windows
The calculate event was being called before the change event & as the calculate event removes the cell comment it destroys the undo stack.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,223
Messages
5,600,399
Members
414,383
Latest member
kevinlarey

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
Top