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

[nmounir]

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?

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

 

[nmounir]

oh i forgot to mention that the error points to application.undo

 

[MARK858]

What happens with

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]

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?

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]

 

[nmounir]

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]

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]

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 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

 

[nmounir]

I forgot to add End Sub in my previous message

it is right after application.enableEvents = True

 

[nmounir]

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]

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


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