After VBA POP-UP need to Undo the previous action

Anbuselvam Kovilmani 41 Reputation points
2022-06-29T11:09:13.293+00:00

Dear Excel Expert

By using the below Sheet1 code I am getting a pop-up Message When I edit Sheet2("E10:G11")

But I need to modify the code to .Undo the previous action after a pop-up message. I don't know how and what code to be changed.

When I edit Sheet2("E10:G11").Value Then Sheet1("K15:M15").Value will change. If the Sheet1 value gets negative then the pop-up will alert the user. At the same time, I want to do Undo the previous action after the alert message.

Workbook Link is below.
https://1drv.ms/x/s!AiSRNak3QYCfg4Q64uYUr1fjjG7aGQ?e=1cGJWx

Please do the modification in the code. Thanks in advance. 

Private Sub Worksheet_Calculate()  
For Each Cell In Range("$K$15:$M$15")  
    If Cell.Value < 0 Then MsgBox "WARNING! RAW MATERIALS Stock is not enough in the RM Code:- " & Cell.Offset(-2, -1).Value & " - " & Cell.Offset(-1, -1).Value, vbOKOnly, "CONSUMPTION IS MORE THAN THE RM STOCK"  
Next Cell  
End Sub
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,456 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,638 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,656 Reputation points
    2022-06-29T11:28:10.083+00:00

    There ist the command
    https://learn.microsoft.com/en-us/office/vba/api/excel.application.undo
    but see remarks: and it must be the first line in the macro
    So I don't think you get it working in your code.

    0 comments
  2. Anbuselvam Kovilmani 41 Reputation points
    2022-06-29T11:42:49.067+00:00

    Thanks for your swift response @Olaf Helper

    I searched on the websites and the forums too.

    I found the below code can do Undo Action

    With Application: .EnableEvents = False: .Undo: .EnableEvents = True: End With

    But I don't know how and where to insert the code.

    Only IF POP-UP Message Comes then it should trigger the undo otherwise no need to run the line.

    Please guide me on how to add those lines to the existing code?

    0 comments