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]

You change Target.Value, but only AFTER that line of code, you turn off the events. Seems to me a strange order of commands.

 

[wigi]

Can you please use

 tags when you paste code on the forum?
Code tags format the code making it easier to read and hence follow the logic of the code.


You can use [CODE] tags in this way: 


Add the word [COLOR=blue][B][PLAIN][code=rich][/PLAIN][/B][/COLOR] before the first line of code, and
add the word [COLOR=blue][B][PLAIN]

[/PLAIN][/B][/COLOR] after the last line of code.


Or: you could use the "#" icon when changing / composing a message in the Advanced editing screen.


Thanks for your consideration.

 

[mef1sto]

Ok no problem got it. But please try and help me. Still in need of help with this error. Target.value could be erased, it's not a problem.

 

[wigi]

At least, put .EnableEvents = False above Target.Value = ""

 

[jsotola]

you would get better help if you provided all the information

from the code i deduced that it is the worksheet change event handler

you have not mentioned what you are trying to achieve, and which values in which cells are causing errors

you are comparing a numerical value to "false"
Len(Target.Value) = False

i think that you want the length of the data to be zero (maybe ... not known what you are trying to do)
Len(Target.Value) = 0

also, you change the cell value, so this routine gets called again

do not use = in boolean comparisons (true/false)

if abc = false then

is same as

if not abc then

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("B16:E28")) Is Nothing Then
        
        If [COLOR=#ff0000]Len(Target.Value) = 0 [/COLOR]Then 'Or IsNumeric(Target) <> True Then


[COLOR=#ff0000]            Target.Value = ""      ' cell change, causes second run of code ( you need to disable events before this )[/COLOR]
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
            End With
            
'            GoTo LetsContinue          ' try to avoid goto statements ( this one appears unnecassary)
            
            Target.Value = ""           ' this line never runs because of the goto command
        End If
    End If


End Sub

 

[mef1sto]

First of all, thank you for your advice, i'll keep in mind your remarks. So on this range (B16:E28) i have put a data validation rule(to not let anyone be able to write data in those cells) and if they tried, i would get a messege(with option Retry and Cancel) the application.undo would delete on cancel whatever was typed in that cell...and this is when the error happened.

 

[mef1sto]

So, now i've put it like this, but still no result:
<code>

 If Not Intersect(Target, Range("B16:E28")) Is Nothing Then
        If Len(Target.Value) = 0 Then  'Or IsNumeric(Target) <> True Then
        With Application
            .EnableEvents = False
        Target.Value = ""
            .Undo
            .EnableEvents = True
        End With
        GoTo LetsContinue
        End If
    End If

</code>

 

[mef1sto]

I take a lucky guess... i think it's because of the range being so, cause i've noticed that this kind of range (e.g.Range("A2:A4,A6,A15,D7:D10,D12:D13,J12,B15:G15,F12,H12,G13")) ) doesn't produce the same output (i refer to an error). Maybe it must be put in another way...which unfortunately i'm not aware of..yet :D.

 

[jsotola]

So on this range (B16:E28) i have put a data validation rule(to not let anyone be able to write data in those cells) and if they tried, i would get a messege(with option Retry and Cancel)

is this what the code is supposed to do?

if it is, why did you not say anything about it in the first post?

btw, you again posted incomplete code

we'll figure this out sooner or later