• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

.Undo code need to perform after accept Vbokonly

Dear Excel Genius

As per the below snapshot, if row 15 gets negative values when I change the values in rows 16 and 17, then I get the pop-up message.

If I press OK then it has to do ".Undo" to get back the previous positive values in row 15.

I used the below code for the pop-up message, need to add the codes to perform undo.

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


79682
 

Attachments

  • Book.xlsb
    14.2 KB · Views: 1
Hi, according to your attachment a VBA event demonstration to paste to the worksheet module :​
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect([K16:M17], Target) Is Nothing Then
        If Cells(15, Target.Column) < 0 Then
            With Application:  .EnableEvents = False:  .Undo:  .EnableEvents = True:  End With
            Beep
        End If
    End If
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
@Marc L

Thanks for your code.

In my actual sheet Intersect reference in another sheet "Sheet2.range("E10:G11")

I don't know how to change the below code line

from
If Not Intersect([K16:M17], Target) Is Nothing Then

to
if not intersect(. ) is nothing then.

I attached the sample file like the actual file which is having the data in two worksheets.

Sorry for my initial post with the different sample data ( I thought I can change the code as per actual data. But I couldn't )
 

Attachments

  • Book.xlsb
    17.1 KB · Views: 2
According to your post #3 attachment :​
• Erase all code in Sheet1 worksheet module …​
• VBA event to paste to Sheet2 worksheet module :​
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect([E10:G11], Target) Is Nothing Then
        If Cells(10, Target.Column) > Cells(11, Target.Column) Then
            With Application:  .EnableEvents = False:  .Undo:  .EnableEvents = True:  End With
            Beep
        End If
    End If
End Sub
You may Like it !
 
Back
Top