exit message box if press close or cancel

[abdelfattah]

hi
I create code to show message based on condition . my formula is based on msgbox =IF(F5<0,MSG(),"")

Private Sub MSG()
Dim Action As VbMsgBoxResult
Action = MsgBox("the date has been expired ", vbOKCancel + vbExclamation + vbDefaultButton2, "warning!")
If Action = vbOK Then
End If

If Action = vbCancel Then Exit Sub

End Sub

so what I want if I press cancel or close button when show the message box based on formula is existed in some cells ,then should the message should close

 

[DanteAmor]

I'm not sure what you ask for. Hope the following can help you.

Private Sub MSG()
  Dim Action As VbMsgBoxResult
  If Range("F5").Value < 0 Then
    Action = MsgBox("the date has been expired ", vbOKCancel + vbExclamation + vbDefaultButton2, "warning!")
    Select Case Action
      Case vbOK
        'here actions
      Case vbCancel
        Exit Sub
    End Select
  End If
End Sub

 

[abdelfattah]

in column g I have many formula

1.xlsm
	F	G
4	-7	0
5	-6	0
6	-5	0
7	-4	0
8	-3	0
9	-2	0
10	-1	0
11	0
12	23
13	23
14	23
15
1

Cell Formulas
Range	Formula
F4:F14	F4	=D4-TODAY()
G4:G14	G4	=IF(F4<0,MSG(),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4:G14	Cell Value	=0	text	NO
F4:F14	Cell Value	>0	text	NO
F4:F14	Cell Value	<0	text	NO

your code works when run macro , but I want run the macro based on column G which contain the formula . if the formula in column G achieve the condition then should show message

 

[DanteAmor]

You want the macro to loop through column G and send a message every time the condition is met.
If the cell meets the condition 10 times, then you want the message 10 times?

 

[abdelfattah]

You want the macro to loop through column G and send a message every time the condition is met.
If the cell meets the condition 10 times, then you want the message 10 times?

yes but if I press close (x) or cancel the message should be gone

 

[DanteAmor]

How about

Private Sub MSG()
  Dim c As Range
  '
  For Each c In Range("F4", Range("F" & Rows.Count).End(3))
    If c.Value < 0 Then
      If MsgBox("the date has been expired ", vbOKCancel + vbExclamation, "warning!") = vbCancel Then Exit Sub
    End If
  Next
End Sub

 

[abdelfattah]

unfortunately doesn't work well . when I press ok continues showing the message and if I press cancel or close reaches for 8 times . the right way when press ok should shows 8 times based on condition in 8 cells and when close or cancel should be the message is closed . should not repeat show for 8 times .

 

[abdelfattah]

I would add this information . the macro in post# 6 works as what I want . just in one case if I run the macro by button . but if I depends on formula in column G

=IF(F4<0,MSG(),"")

it will occurs the problems as I have ever mentioned in post#7

 

[DanteAmor]

the macro in post# 6 works as what I want . just in one case if I run the macro by button

That's what you asked for. A macro that runs through the entire column F. And the message appears in each cell when the cell meets the condition.
I don't see any sense in your formula. All formulas are executed. They do not depend on a button to cancel. You cannot stop the execution of the formulas, unless you turn off all the formulas.

 

[abdelfattah]

based on this

All formulas are executed. They do not depend on a button to cancel. You cannot stop the execution of the formulas, unless you turn off all the formulas.

I decided put the macro in workbook open event and delete the column G instead of put the formula and show problem by repeat showing the message.
many thanks for this macro .it helps me so much