Public Function

[Guest]

My worksheet has the following:-

In Cell B1to B100 a received invoice date is input
In Cell C1to C100 a received invoice date at cost center is input
In Cell D1to D100 the following formula is input to calculate the
number of days =DAYS360(B1,C1)

What I am trying to achieve is if the number of days
calculated is over 15 Days, staff have to issue a letter to the budget
holder to ask them to explain the lateness of the invoice.
So if I could get some sort of message box to appear this
will ensure that this is done.

I am able to run a validation and change the cell to red, however staff
still forget to issue letter.
So can anyone help me to sort this problem?

I have already tried the following but no joy, error circular ref:-


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

Then in cell D1 I input the following:-
=DAYS360(B1,C1)=IF(D1>15,PromptPayment(),"")

and if i just input this instead
=IF(D1>15,PromptPayment(),"")

 

[Don Guillett]

try this formula in col D
=IF(DAYS360(B5,C5)>=15,"Do Letter",DAYS360(B5,C5))
or
=IF(DAYS360(B5,C5)>=15,"Do Letter","OK")

 

[JE McGimpsey]

Please follow your previous threads instead of creating new ones.

 

[Otto Moehrbach]

Monty
A formula in a cell can only return a value to that cell, it cannot
trigger a macro or a function to run.
To get a message box to display, you will need VBA. From what you say,
you need a macro to check all the values in Column D and for each value that
is over 15, display a message box citing some identifying parameter of that
row and stating what has to be done.
The only question that has to be decided, and only you can do that, is
when to run that macro. Again from what you say, I think that you need to
run that macro automatically upon the occurrence of some event. Excel
recognizes many events and can act automatically when these events occur.
Only you know which event would be best for your problem.
Some events that I think could work for you are:
Before_Save event - action is taken when the user saves the file.
Before_Close event - action is taken when the user closes the file.
Before_Print event - action is taken when anything is printed from the file.
Calculate event - action is taken when the sheet is calculated.

There are many more events but these appear to be germane to your problem.
Post back if any of the above looks to you like something you might want.
HTH Otto

 

[JE McGimpsey]

I made up an example workbook for you using both C1-B1 and Days360() at

ftp://ftp.mcgimpsey.com/excel/monty_demo.xls

 

[JE McGimpsey]

Actually, in Monty's case, since the function is only throwing up a
msgbox, it works fine, he just needs to get his formulas correct:

D1: =IF(DAYS360(B1,C1)>15,PromptPayment(),DAYS360(B1,C1)

or

D1: =IF((C1-B1)>15,PromptPayment(),C1-B1)

 

[Guest]

Hi

Sorry about all this i am just starting to learn about excel, as you can tell.
it is also the first time i have asked questions in this way.
I have copied the example workbook using your formula below and it works
like a dream thanks for that.
however can i ask you one more question.
When you hit the ok button in the message box the cell reverts back to zero,
can this be changed so the correct amount of days are shown even if they are
over 15.
Thanks once again for all your help.
Monty

 

[JE McGimpsey]

One way:

=IF((C1-B1)>=15,PromptPayment()+C1-B1,C1-B1)

 

[Guest]

what cell would i put this into

One way:

=IF((C1-B1)>=15,PromptPayment()+C1-B1,C1-B1)

 

[Guest]

Sorry got this working okay

thanks for all your help

Monty