method undo of object _application failed

[mef1sto]

Hi guys! Here's another error that bothers me lately.The code is:
If Not Intersect(Target, Range("B16:E28")) Is Nothing Then
If Len(Target.Value) = False Then 'Or IsNumeric(Target) <> True Then
Target.Value = ""
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
GoTo LetsContinue
Target.Value = ""
End If
End If

but it keeps showing that error twice and it actually does the undo. Can anyone tell me another way of solving this? Already tried my best, but not good enough. Thanks!

 

[wigi]

we'll figure this out sooner or later

Is that a question that you have there?

 

[mef1sto]

Well yes that's what it supposed to do. And ya that's bout it, that's the code that should eliminate that error, though it doesn't and that's annoying!

 

[jsotola]

try this

it works for a single cell change

more code is needed if multiple cells are changed

the save and restore would be done to an array

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim newData As Variant
    Dim oldData As Variant
    
    If Intersect(Target, Range("B16:E28")) Is Nothing Then Exit Sub    ' not in watched range
    
    Application.EnableEvents = False
            
    newData = Target.Value     ' save new value
    Application.Undo
    oldData = Target.Value     ' save old value
    Target.Value = newData
    
    If Not MsgBox("do you really want to change cell?", vbYesNoCancel, "protected data") = vbYes Then
        Target.Value = oldData
    End If
    
    Application.EnableEvents = True


End Sub

 

[mef1sto]

Thanks so much for replying to my post. So guys, this is what happens. Your code is great, but this error shows up when my cell is empty and i try to write something in it and thanks to the data validation rule i get a warning message saying to retry, cancel, or help, 3 buttons. If i choose the cancel button this is when the error appears twice actually, i press ok and the undo thing happens correctly. I believe i would need to catch the Cancel button event in a sort of select case, and in case this button is pressed to do the undo action. What do you think?

 

[mef1sto]

So this is how i guess i solved this issue:

Dim iRet As Integer
          
            ' Check pressed button
            If iRet = vbCancel Then
               Application.Undo
            End If

 

[mef1sto]

Actually that didn't work to well either so i successfully tried this way:

Set rnga12 = Range("B16:E28")
    If Not Intersect(Target, rnga12) Is Nothing Then
        If Len(rnga12.Cells(1, 1).Value) = 0 Then
               Application.Undo
        End If
    End If

 

[mef1sto]

Update:

Set rnga12 = Range("B16:E28")
    For Each cell In rnga12
    
    If Not Intersect(Target, cell) Is Nothing Then
        If cell.Value = 0 Or cell.Value <> 0 Then
            MsgBox "Update cell denied!", vbCritical
            Application.Undo
        End If
    End If
    Next cell

 

[jsotola]

Update:

Set rnga12 = Range("B16:E28")
    For Each cell In rnga12
    
    If Not Intersect(Target, cell) Is Nothing Then
        If cell.Value = 0 Or cell.Value <> 0 Then
            MsgBox "Update cell denied!", vbCritical
            Application.Undo
        End If
    End If
    Next cell

you still have not posted all of your code

the "Sub ....." and "End Sub" lines are missing

if you posted at least the complete begining of your code, you would have had your solution several days ago

it makes it very difficult to help you with your problem because we do not know which event calls this code

the code that you are asking for, can go into several event handlers, but it will only work reliably in one or two of them


what you are doing, is like telling someone that you have watched 5 movies (a,b,c,d,e,f)
and ask that person to explain the meaning of the second scene in the movie,
you do not tell them the name of the movie,
but you expect them to give you the correct answer on the first try

it is very confusing,

i am also beginning to suspect that you may have other code running

 

[mef1sto]

Ok sure your right i'm a bit confusing but i thought you could figure out another way maybe to solve my case. So this is the code with its event handler:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.ProtectContents = False Then
On Error GoTo Whoa
Application.EnableEvents = False
Application.ScreenUpdating = False
If Not Intersect(Target, Range("A2:A4,A6,A15,A16:A28,D7:D10,D12:D13,J12,B15:G15,F12,H12,G13")) Is Nothing Then
If Len(Target.Value) = 0 Then
Application.Undo
End If
End If

Set rnga12 = Range("B16:E28")
For Each cell In rnga12
If Not Intersect(Target, cell) Is Nothing Then
If cell.Value = 0 Or cell.Value <> 0 Then
MsgBox "Nu puteti edita acest camp!", vbCritical
Application.Undo
End If
End If
Next cell

LetsContinue:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue

End If
End Sub

That's it. I now hope for the most desired solution of mine

 

[jsotola]

thank you

working on it ...

few questions just to clarify

1. Range("B16:E28") 

     ... it does not allow any changes, did you want yes/no dialog to accept change?

2. Range("A2:A4,A6,A15,A16:A28,D7:D10,D12:D13,J12,B15:G15,F12,H12,G13") 

     ... it allows value to be entered, but does not allow deletion,
         but it will allow a space, which looks like a deletion
         do you want to prevent blanking cell with spaces ?
         did you want yes/ no dialog for this range to accept an actual deletion ?

3.  do you want to allow multiple cells to be changed (copy/paste functions) or just one at a time ?    


the code you have contains a bug

if multiple cells are changed at the same time, then undo gets called more than once, and screws things up

undo needs to be called only once

single-stepping through the code confirms it