Application.Undo

[mikke3141]

Hello,

When running the following macro, I get error message: Method 'Undo' of object ' _Application' failed, and then excel closes down.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$2" Then
    Dim RE As Object
    Set RE = CreateObject("vbscript.RegExp")
    RE.Pattern = "^(0[1-9]|[12]\d|3[01])(0[1-9]|1[0-2])\d\d[+\-A]\d{3}[0-9ABCDEFHJKLMNPRSTUVWXY]"
    Cells(2, 2) = UCase(Cells(2, 2))
        If RE.test(Cells(2, 2)) = False Then
        MsgBox "The cell value is wrong format. Correct format is 311299-1166A"
        Application.Undo
        Exit Sub
        End If
End If
End Sub

What could be the cause? Thank you for your help.

 

[pgc01]

Hi Mike

I see 2 problems in the code

1 - you can only use .Undo to undo the last action in the worksheet. You cannot use it to undo vba actions. Since you do an assignment ("Cells(2, 2) = UCase(Cells(2, 2))") you can no longer use the .Undo

2 - you are undoing an assignment to B2 and so you must disable the events, or else you'd be caught in an endless assign/undo loop

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RE As Object
 
If Target.Address = "$B$2" Then
    Set RE = CreateObject("vbscript.RegExp")
    RE.Pattern = "^(0[1-9]|[12]\d|3[01])(0[1-9]|1[0-2])\d\d[+\-A]\d{3}[0-9ABCDEFHJKLMNPRSTUVWXY]"
    RE.IgnoreCase = True
    
    If RE.test(Cells(2, 2)) = False Then
        MsgBox "The cell value is wrong format. Correct format is 311299-1166A"
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
    Else
        With Application
            .EnableEvents = False
            Cells(2, 2) = UCase(Cells(2, 2))
            .EnableEvents = True
        End With
    End If
End If
End Sub

 

[pgc01]

P. S.

I don't know what you are testing and so I don't know if this is relevant but you are only testing the first characters of the code, and so "311299-1166A" works ok, but "311299-1166A-I- CAN-WRITE-WHATEVER-ELSE-I-WANT" will also work. Is this what you want or should the code end after the first "A" in your example?

 

[mikke3141]

Thank you PGS for your help