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:
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.
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
is it possible to share the sheet with you on here? this way you would be able to see the issue exactly.?
 
Upvote 0
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.
 
Upvote 0
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.

 
Upvote 0
I tried changing A1 to AAA and got the result below

1602953301443.png
 
Upvote 0
Have you enabled " Trust access to the VBA project object model"?
 
Upvote 0
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 !
 
Upvote 0
@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.
 
Upvote 0
Ok, that's odd, when you try it does the change event run before the calculate event?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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