Module or Formula change

[Monty]

Hi

I have a worksheet made up for different cost centers
with reference to invoices. For example who they are
from, what date is on the invoice, what date did we
receive it, amount, etc. I have written the following
module and formula (see below for details) in order to
bring up a message box if the amount of days between two
dates are greater than 15. The reason for this is that we
have a turnaround time of 14 days to pay these invoices
and staff forget to issue a late letter if the invoice is
paid outside the 15 days.
This works great, the message box appears when the days
are greater than 15 days, the only problem is when you go
to open the spreadsheet after closing it down the message
box will appear again before the full sheet opens.
Do I need to add something to the Module below to say If
this is true then do this or do I need to add something
to the formula. As you are probably aware I am a novice
and any help would be greatly appreciated

Module
Option Explicit

Public Function PromptPayment()
MsgBox "Please issue Prompt Payment Letter and input
relevant information to PP spreadsheet"

End Function

Formula
=IF((F4-E4)>=15,PromptPayment()+F4-E4,F4-E4)


Thanks

 

[Dave Peterson]

I try to not use MsgBoxes in userdefined functions. This notice will appear
each time that formula is recalculated. So it could be more often.

I think I would just use an adjacent cell (formatted in Big Bold Red letters)
with a formula like:

=IF((F4-E4)>=15,"Please Issue ..etc...","")

You may even want to add a cell that allows the user to indicate that they did
what they were supposed to:

=if(and((f4-e4)>=15,g4<>"done"),"Please Issue...etc","")

Then they mark the cell and the message goes away.

 

[Dave Peterson]

Another thing I like about using that worksheet cell--User's (including me) can
dismiss that message box and never read it. With that big, bold red letters, it
would be more difficult to ignore/miss.

 

[Monty]

thanks for this i will give it a go.
i think i have found the problem. i am running this
spreadsheet through a database in lotus notes.
Is there a problem with running VBA or macros in lotus
notes and if there is is there any remedy.
thanks

 

[Dave Peterson]

I've never used Lotus Notes.

Maybe someone can chime in.

 

[Guest]

Is there anyway that when you type done in the cell it
will return the total days between the two dates. as this
information is still needed.

thanks

 

[Dave Peterson]

=if(g4="done",f4-e4,if(f4-e4>=15,"Please Issue...",""))

 

[Guest]

Thanks

This works great, however the only problem is it will not
return the amount of days if it is below 15. any
suggestions.

 

[Dave Peterson]

Maybe...

=IF(G4="done",F4-E4,IF(F4-E4>=15,"Please Issue..."&F4-E4&" days",""))

I'm kind of lost on what you're trying to do.