message box

[Monty]

Hi all,

At present i have to cells with dates input by staff, in the third cell the
number of days are calculated. can i run a formula to bring up a message box
to say issue prompt payment letter if the days calculated are over 15.
thanks

 

[Don Guillett]

Yes, you could use a worksheet_change event triggered by input into the 2nd
cell.

 

[Mark]

How can you do this??

Thanks

-----Original Message-----
Yes, you could use a worksheet_change event triggered by input into the 2nd
cell.

--
Don Guillett
SalesAid Software
(e-mail address removed)

(e-mail address removed)... in the third cell
the bring up a message
box


.

 

[Don Guillett]

right click worksheet tab>view code>copy/paste this>modify to suit>SAVE
Now, put a numbe in l5 and watch what happens.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$L$5" Then Exit Sub
Target.Offset(1, 4) = Target * 3
End Sub

 

[Monty]

sorry tried this no luck!!

right click worksheet tab>view code>copy/paste this>modify to suit>SAVE
Now, put a numbe in l5 and watch what happens.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$L$5" Then Exit Sub
Target.Offset(1, 4) = Target * 3
End Sub

 

[Don Guillett]

It does work!! What did you do?

 

[Mark]

Hi

I followed your instructions below and copied then pasted.

In Cell A1 i input 12/10/04

In Cell B1 i input 12/12/04

In Cell C1 i input the formula =DAYS360(A1,B1) which
returned the number 60. nothing else happened.

CAn you please help.
MArk




-Original Message-----

 

[Don Guillett]

Did you notice the first line and my statement about cell L5?

If Target.Address <> "$L$5" Then Exit Sub

Now, put a numbe in l5 and watch what happens.
change to B1 if that is the ONLY cell you want to influence this
calculation.

 

[Monty]

Still no joy with this nothing happens.
when i hit run in the microsoft visual basic it comes up with a macros
screen which is blank, so i have to cancel .
any ideas will be welcome

 

[Don Guillett]

What I gave you originally was an example of how a worksheet_change event
works.

You do NOT put in the ThisWorkbook or a regular module. It goes in the SHEET
module as per my instruction of "right click sheet tab>view code>copy/paste"

The macro will fire AUTOMATICALLY when you enter something in the referenced
cell. It is usually restricted to a column and below a row. In the example I
sent, I restricted to cell L5, AS AN EXAMPLE.

Exactly what did you modify the macro to? Copy/paste here.

 

[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")