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 them, 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
run auto filter the message box will appear again before
searching under filter.
I originally had conditional formatting in the cells but
staff either forgot or just didn't think to issue a late
letter invoice
Can I add something to the module (see below) to say
delete the formula in that cell once the ok button has
been hit or activated? This would I think fix the problem

Module
Option Explicit

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

End Function

Formula in Cells
=IF((F4-E4)=15,PromptPayment()+F4-E4,F4-E4)
Thanks

Mark

No, a function can return a result (and give a MsgBox as you found), but it
cannot modify the contents of the cell, or any other cells.

--
HTH

Bob Phillips

"Mark" wrote in message
...

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 them, 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
run auto filter the message box will appear again before
searching under filter.
I originally had conditional formatting in the cells but
staff either forgot or just didn't think to issue a late
letter invoice
Can I add something to the module (see below) to say
delete the formula in that cell once the ok button has
been hit or activated? This would I think fix the problem

Module
Option Explicit

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

End Function

Formula in Cells
=IF((F4-E4)=15,PromptPayment()+F4-E4,F4-E4)
Thanks

Mark