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:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

nmounir

New Member
Joined
Oct 16, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
oh i forgot to mention that the error points to application.undo
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What happens with
Rich (BB code):
Private Sub Worksheet_Calculate()

    Dim Waterbblperbbl As Range

    Set Waterbblperbbl = Range("F22")
   
    Application.EnableEvents = False

    If Waterbblperbbl > 0 Then

        Waterbblperbbl.ClearComments

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

    Else

        Waterbblperbbl.ClearComments

    End If

    Application.EnableEvents = True
   
End Sub
 

nmounir

New Member
Joined
Oct 16, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Thank you Rich. I tried it but it still doesn't work and i am getting the same error. Please note that I actually have 2 codes in my calculate event as follow:

so i placed the application.enableevents = false and application.enableevents = true in both of them? is that wrong? could this be the issue?

VBA Code:
Private Sub Worksheet_Calculate()

    Dim Waterbblperbbl As Range

    Set Waterbblperbbl = Range("F22")
 
    [B]Application.EnableEvents = False[/B]

    If Waterbblperbbl > 0 Then

        Waterbblperbbl.ClearComments

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

    Else

        Waterbblperbbl.ClearComments

    End If

    [B]Application.EnableEvents = True[/B]

Second Calculate event code starts here

    Dim Waterbblperbbl As Range

    Set Waterbblperbbl = Range("F21")
 
    [B]Application.EnableEvents = False[/B]

    If Waterbblperbbl > 0 Then

        Waterbblperbbl.ClearComments

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

    Else

        Waterbblperbbl.ClearComments

    End If

    [B]Application.EnableEvents = True
[/B]
 
Last edited by a moderator:

nmounir

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

ADVERTISEMENT

I tried with one code only by adding application.enableevents = false and application.enableevents = true but still same application.undo (Method'Undo of Object' _ application failed error pops up pointing to the change event.

it is very puzzling !
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Can you please post all your codes from the first lines to the End Sub lines please (please can you also use code tags, see my signature block below for how to this).
 

nmounir

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

ADVERTISEMENT

Sure please see below all the codes i have in the sheet. two events ( first one is Calculate event and second is a change event). when i remove / delete the calculate event, the change one would work perfectly. there seems to be some incompatibility between the two events. I copied and pasted and formatted them up to my knowledge. I apologize if my copy paste below is not perfect. I started learning VBA few weeks ago only !

VBA Code:
Private Sub Worksheet_Calculate()

    Dim Waterbblperbbl As Range
    Set Waterbblperbbl = Range("F22")
    Application.EnableEvents = False
    If Waterbblperbbl > 0 Then
    Waterbblperbbl.ClearComments
    Waterbblperbbl.AddComment ("bbl of water per 1 bbl of mud")
    Else
    Waterbblperbbl.ClearComments
    End If
    Application.EnableEvents = True


    Dim Waterbblperbbl As Range
    Set Waterbblperbbl = Range("F21")
    Application.EnableEvents = False
    If Waterbblperbbl > 0 Then
    Waterbblperbbl.ClearComments
    Waterbblperbbl.AddComment ("bbl of water per 1 bbl of mud")
    Else
    Waterbblperbbl.ClearComments
    End If
    Application.EnableEvents = True

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
    Application.EnableEvents = True
 
Last edited by a moderator:

nmounir

New Member
Joined
Oct 16, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
I forgot to add End Sub in my previous message

it is right after application.enableEvents = True
 

nmounir

New Member
Joined
Oct 16, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Sure please see below all the codes i have in the sheet. two events ( first one is Calculate event and second is a change event). when i remove / delete the calculate event, the change one would work perfectly. there seems to be some incompatibility between the two events. I copied and pasted and formatted them up to my knowledge. I apologize if my copy paste below is not perfect. I started learning VBA few weeks ago only !

Private Sub Worksheet_Calculate()

Dim Waterbblperbbl As Range
Set Waterbblperbbl = Range("F22")
Application.EnableEvents = False
If Waterbblperbbl > 0 Then
Waterbblperbbl.ClearComments
Waterbblperbbl.AddComment ("bbl of water per 1 bbl of mud")
Else
Waterbblperbbl.ClearComments
End If
Application.EnableEvents = True


Dim Waterbblperppb As Range
Set Waterbblperppb = Range("F21")
Application.EnableEvents = False
If Waterbblperppb > 0 Then
Waterbblperppb.ClearComments
Waterbblperppb.AddComment ("bbl of water per 1 bbl of mud")
Else
Waterbblperppb.ClearComments
End If
Application.EnableEvents = True

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
Application.EnableEvents = True
End Sub

I fixed the code. please ignore previous message.
 

nmounir

New Member
Joined
Oct 16, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Private Sub Worksheet_Calculate()

VBA Code:
Dim Waterbblperbbl As Range
Set Waterbblperbbl = Range("F22")
Application.EnableEvents = False
If Waterbblperbbl > 0 Then
Waterbblperbbl.ClearComments
Waterbblperbbl.AddComment ("bbl of water per 1 bbl of mud")
Else
Waterbblperbbl.ClearComments
End If
Application.EnableEvents = True


Dim Waterbblperppb As Range
Set Waterbblperppb = Range("F21")
Application.EnableEvents = False
If Waterbblperppb > 0 Then
Waterbblperppb.ClearComments
Waterbblperppb.AddComment ("bbl of water per 1 bbl of mud")
Else
Waterbblperppb.ClearComments
End If
Application.EnableEvents = True


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
Application.EnableEvents = True
End Sub
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,595
Members
414,462
Latest member
StageRiis

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