I have a sheet that serves as a summary card for job data. Cell E18 has a
formula that finds and returns the value of the consumable levy on the job
card. Is there a way to prompt or remind users to check this if the value is
zero? A user will almost always need to print the summary, so before
printing may be a good time to remind them.

Hope you can help.


--
Jim

hi,
try this...
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rng As Range
Set rng = Range("E18")
If IsEmpty(rng) Then
MsgBox ("cell E18 is zero or empyt." & vbNewLine _
& "Printing canceled.")
Cancel = True
End If
End Sub

regards
FSt1

"Jim G" wrote:

I have a sheet that serves as a summary card for job data. Cell E18 has a
formula that finds and returns the value of the consumable levy on the job
card. Is there a way to prompt or remind users to check this if the value is
zero? A user will almost always need to print the summary, so before
printing may be a good time to remind them.

Hope you can help.


--
Jim

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Sheets("Sheet1").Cells(18, 5).Value = 0 Then
MsgBox "Please check cell E18, value shouldnt be zero", vbCritical
Cancel = True
End If
End Sub

"Jim G" wrote:

I have a sheet that serves as a summary card for job data. Cell E18 has a
formula that finds and returns the value of the consumable levy on the job
card. Is there a way to prompt or remind users to check this if the value is
zero? A user will almost always need to print the summary, so before
printing may be a good time to remind them.

Hope you can help.


--
Jim

Hi

You could use conditional formatting so it's really obvious and 'in their
face' when it isn't equal to zero.

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

If this post answered your question please let us know as others may be
interested too


"Jim G" wrote:

I have a sheet that serves as a summary card for job data. Cell E18 has a
formula that finds and returns the value of the consumable levy on the job
card. Is there a way to prompt or remind users to check this if the value is
zero? A user will almost always need to print the summary, so before
printing may be a good time to remind them.

Hope you can help.


--
Jim

My condition has now changed to alert the user if the value is NOT ZERO
(policy change) and is in a list of 5 other pieces of financial information
and I'm afraid this might get lost with all the other information. I'm also
a little concerned that I have so much other conditonal formatting the users
might 'switch' off to the alerts. This condition is one where I really need
to stop them in their tracks before they proceed.

--
Jim


"aneasiertomorrow" wrote:

Hi

You could use conditional formatting so it's really obvious and 'in their
face' when it isn't equal to zero.

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

If this post answered your question please let us know as others may be
interested too


"Jim G" wrote:

I have a sheet that serves as a summary card for job data. Cell E18 has a
formula that finds and returns the value of the consumable levy on the job
card. Is there a way to prompt or remind users to check this if the value is
zero? A user will almost always need to print the summary, so before
printing may be a good time to remind them.

Hope you can help.


--
Jim

I have copied it into the code of the sheet and neither of these seem to work.

The cell E18 has a formula. Would this effect the test for value?

BTW: we have changed our policy and the test is now if E18 is NOT zero.

--
Jim


"Himani" wrote:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Sheets("Sheet1").Cells(18, 5).Value = 0 Then
MsgBox "Please check cell E18, value shouldnt be zero", vbCritical
Cancel = True
End If
End Sub

"Jim G" wrote:

I have a sheet that serves as a summary card for job data. Cell E18 has a
formula that finds and returns the value of the consumable levy on the job
card. Is there a way to prompt or remind users to check this if the value is
zero? A user will almost always need to print the summary, so before
printing may be a good time to remind them.

Hope you can help.


--
Jim

Oh well, looks like the VBA solution then :-)

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

If this post answered your question please let us know as others may be
interested too


"Jim G" wrote:

My condition has now changed to alert the user if the value is NOT ZERO
(policy change) and is in a list of 5 other pieces of financial information
and I'm afraid this might get lost with all the other information. I'm also
a little concerned that I have so much other conditonal formatting the users
might 'switch' off to the alerts. This condition is one where I really need
to stop them in their tracks before they proceed.

--
Jim


"aneasiertomorrow" wrote:

Hi

You could use conditional formatting so it's really obvious and 'in their
face' when it isn't equal to zero.

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

If this post answered your question please let us know as others may be
interested too


"Jim G" wrote:

I have a sheet that serves as a summary card for job data. Cell E18 has a
formula that finds and returns the value of the consumable levy on the job
card. Is there a way to prompt or remind users to check this if the value is
zero? A user will almost always need to print the summary, so before
printing may be a good time to remind them.

Hope you can help.


--
Jim

I have a similar warning (provided by another helper) on the sheet so I tried
this:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "f22:f23" '<== change to suit
Const CON_RANGE As String = "e18:e18" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "Yes" Then
MsgBox "You have choosen to invoice OPEN PO's. You must
deliver the goods/services or advise accounting staff before you proceed."
'.Value = ""
End If
End With
End If
If Not Intersect(Target, Me.Range(CON_RANGE)) Is Nothing Then
With Target
If .Value < "0" Then
MsgBox "The Value of Consumables is not set to ZERO-Please
Check"
'.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This probably a bit crude by your standards and only works if I replace the
formula with a value that is not zero. If I change the underlying data to a
value other than 0, it does not recognise that it is not zero.

This is not a before print test but it would serve my purpose.


--
Jim


"aneasiertomorrow" wrote:

Hi

You could use conditional formatting so it's really obvious and 'in their
face' when it isn't equal to zero.

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

If this post answered your question please let us know as others may be
interested too


"Jim G" wrote:

I have a sheet that serves as a summary card for job data. Cell E18 has a
formula that finds and returns the value of the consumable levy on the job
card. Is there a way to prompt or remind users to check this if the value is
zero? A user will almost always need to print the summary, so before
printing may be a good time to remind them.

Hope you can help.


--
Jim