formula or code

[Monty]

if i wanted a message to appear on screen (saying please issue prompt payment
letter)if a certain number in a cell was over 15.
how would i go about this.
i am a novice at this any help please.
thanks.

 

[Nick Hodge]

Monty

If you can set up a formula next to the cell then you could use an IF
function, like

=IF(CellWith15In<15,"","Issue Prompt Payment Letter")

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[JE McGimpsey]

One way:

=IF(G4>15,"Please Issue Prompt Payment Letter", "")

If you want a message box instead, use

=IF(G4>15,PromptPayment(),"")

with this UDF in a regular code module:

Public Function PromptPayment()
MsgBox "Please issue Prompt Payment Letter"
End Function

If you're unfamiliar with UDFs, see David McRitchie's "Getting Started
with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Monty]

Thanks for this
but can i ask you another point.

If i had the followinig formula in box G4 =DAYS360(E34,F34)and i wanted to
add your message box formula =IF(G4>15,PromptPayment(),"")to it. how would i
do this.
thanks once again.

 

[Guest]

Hi

me again

When i put any of these formulas in it comes up with an
circular reference error?? Any ideas.

thanks

-----Original Message-----
One way:

=IF(G4>15,"Please Issue Prompt Payment Letter", "")

If you want a message box instead, use

=IF(G4>15,PromptPayment(),"")

with this UDF in a regular code module:

Public Function PromptPayment()
MsgBox "Please issue Prompt Payment Letter"
End Function

If you're unfamiliar with UDFs, see David McRitchie's "Getting Started
with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[JE McGimpsey]

Put them in a cell other than the one you're monitoring...

If G4 has an existing calculation, say,

=SUM(G1:G3)

and you want the message in the cell, use

=IF(SUM(G1:G3)>15,"Please issue Prompt Payment Letter", SUM(G1:G3))

If you want a message box, the easiest thing to do is use the
Worksheet_Calculate() event. Put this in your worksheet code module
(right-click the worksheet tab and choose View Code):

Private Sub Worksheet_Calculate()
With Range("G4")
If IsNumeric(.Value) Then _
If .Value > 15 Then _
MsgBox "Please Issue Prompt Payment Letter"
End With
End Sub

This will automatically monitor G4 every time the sheet calculates

 

[JE McGimpsey]

One way:

=IF(DAYS360(E34,F34)>15,PromptPayment(),DAYS360(E34,F34))

 

[Monty]

Sorry

If i put this formula =IF(G4>15,PromptPayment(),"")it comes up with the following error #NAME?

can anyone please help

Hi

me again

When i put any of these formulas in it comes up with an
circular reference error?? Any ideas.

thanks

 

[JE McGimpsey]

If PromptPayment is stored in the same workbook, or an add-in, it should
be in a regular code module, not a workbook or worksheet class module
(see

http://www.mcgimpsey.com/excel/modules.html

for more).

If PromptPayment() is stored in another workbook (like Personal.xls)
that needs to be referenced, e.g.:

=IF(G4>15,Personal.xls!PromptPayment(),"")

 

[Guest]

Thanks

For this
I still have one problem with this.
it works perfectly when you input the relevant info and
the days are over 15 days it will come up with a message
box. The problem is when you re-open this workbook the
message box appears before it opens! how can i stop this
from happening.

thanks.

Mark

-----Original Message-----
One way:

=IF(G4>15,"Please Issue Prompt Payment Letter", "")

If you want a message box instead, use

=IF(G4>15,PromptPayment(),"")

with this UDF in a regular code module:

Public Function PromptPayment()
MsgBox "Please issue Prompt Payment Letter"
End Function

If you're unfamiliar with UDFs, see David McRitchie's "Getting Started
with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thanks

For this
I still have one problem with this.
it works perfectly when you input the relevant info and
the days are over 15 days it will come up with a message
box. The problem is when you re-open this workbook the
message box appears before it opens! how can i stop this
from happening.

thanks.

Mark

-----Original Message-----
One way:

=IF(G4>15,"Please Issue Prompt Payment Letter", "")

If you want a message box instead, use

=IF(G4>15,PromptPayment(),"")

with this UDF in a regular code module:

Public Function PromptPayment()
MsgBox "Please issue Prompt Payment Letter"
End Function

If you're unfamiliar with UDFs, see David McRitchie's "Getting Started
with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm