Adding an error message at close of file when criteria are met

[Dave]

How do I create an error message that will pop up when certain criteria are
met. I have a file that compares prior and current month changes. I would
like the file to give an error message, when it is closed, if the variance is
greater than $5,000 or 10%. I would like the message to give the option to
cancel save so the manager can go in and make changes. If no changes are
necessary, the manager should be able to save the file and exit.

Also, in the same file, I would like to use conditional formatting to
highlight the current month cells that correspond to variances listed above.
My problem is the variance is calculated in another cell that is not visible
to the manager. An example is: current amount entered in cell A1; prior
dollar amount is in HY1, dollar variance is calculated in HZ1 and percent
variance is calculated in IA1. I would like A1 to be highlighted if either
HZ1 or IA1 meet the variances listed above.

Please let me know if I need to post this to one of the other boards.

Thanks

 

[Bob Phillips]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
dim ans
If Worksheets("Sheet1").Range("A1").Value > 5000 Then
ans = MsgBox("Variance too high, correct it?", vbOKCancel)
If ans = vbOK Then
Cancel = True
End If
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


For the CF, in A1 use a formula of

=OR(HZ$1>5000,IA$1>5000)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave]

Hi Bob

Thanks for the information. I have one more question. In the entry for the
error message, is there a way to evaluate two different cells. I need to
check A1 to see if it is >5000 and B1 to see if it is >10%. If either or
both of the criteria are met, I need to display the error box. What do I
need to change to accomplish this?

Thanks again.

 

[Bob Phillips]

Change this

If Worksheets("Sheet1").Range("A1").Value > 5000 Then

to

If Worksheets("Sheet1").Range("A1").Value > 5000 And _
Worksheets("Sheet1").Range("B1").Value > .1 Then


--

HTH

RP
(remove nothere from the email address if mailing direct)