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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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)


"Dave" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >