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
I don't get an error other than a Duplicate declaration in scope error because you have
VBA Code:
Dim Waterbblperbbl As Range
twice in the Calculate routine.

I also removed the EnableEvents code and it still worked.

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
   


 
    Set Waterbblperbbl = Range("F21")
 
    If Waterbblperbbl > 0 Then
        Waterbblperbbl.ClearComments
        Waterbblperbbl.AddComment ("bbl of water per 1 bbl of mud")
    Else
        Waterbblperbbl.ClearComments
    End If


End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("$D$15").Address Then
        Range("$F$15").ClearContents
    End If

    If Target.Address = "$AJ$3" Then
        Application.EnableEvents = False
        If Range("AJ3").Value <> Sheet4.Range("E7") Then
            Application.Undo
            MsgBox "This cell content cannot be changed"
        End If
    End If

End Sub

I can't help any further as I can't replicate your error I'm afraid.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

nmounir

New Member
Joined
Oct 16, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
is it possible to share the sheet with you on here? this way you would be able to see the issue exactly.?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You can upload it to a free file hosting site like www.box.com or www.dropbox.com, mark it for sharing and paste the link it produces in the thread and I'll have a look.
Make sure that you amend any sensitive data before uploading.
 

nmounir

New Member
Joined
Oct 16, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
I made sure no sensitive information in the sheet. In there I created two events, the change event is in Cell A1 and the Calculate event is in cell H36. Both are highlighted in Yellow. if you try to change the content of cell A1, you would get the "Method 'Undo of Object'_Application failed" error. The Calculate event is in Cell H36 and its value changes according to the date you select in cell C30.

Thank you so much for your help and patience.

 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

I tried changing A1 to AAA and got the result below

1602953301443.png
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Have you enabled " Trust access to the VBA project object model"?
 

nmounir

New Member
Joined
Oct 16, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Woow so it worked with you !! nice

No I have not enabled "Trust access to the VBA project object model". I do not know what this does?

Do you think this could be the reason why I am getting this "Method 'undo of Object application failed error".

will enable it and let you know.

Thank you so much !
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,321
Office Version
  1. 365
Platform
  1. Windows
@MARK858
Did you have calculation set to manual when you tried it?
When I change the value in A1, the calculate event kicks in (because of the volatile functions) which clears any comments in H36, which in turn clears the undo stack.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@Fluff No I am on Automatic.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,321
Office Version
  1. 365
Platform
  1. Windows
Ok, that's odd, when you try it does the change event run before the calculate event?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,422
Messages
5,601,562
Members
414,458
Latest member
mekhallet salah

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