On Mon, 20 Sep 2004 19:38:06 -0700, "Robert Christie"
wrote:

From Sebastienm's previous post I believe a User Defined
Function is what I require.

Then you can use the simple UDF that I posted in the previous message (which
uses the same algorithm as my formula):

==============
Function Last5th(dt) As Date
Last5th = dt + 1 - Day(dt - 4)
End Function
==============



I will attach the code/macro to a screen button to
change an existing date in B2 to always be the 5th of a
month.


A UDF is used as a formula. To attach to a button, you need a SUB procedure.

====================
Sub Last5th()
Dim dt As Date
Dim rg As Range

Set rg = [b2]

If IsDate(rg) Then rg = rg - Day(rg - 4) + 1

End Sub
==================

The date steps down 1 month on each click on the screen
button.

Well your original request won't do that.

Your original request said that if the date was on the 5th of the month, you
wanted to keep it on the 5th of the month. In order for it to step down 1
month, it would have to go to the previous 5th if it was the 5th. But then you
have the problem of what to do if the user enters the 5th -- stay the same or
drop back. If you want it to stay the same if the user entered the 5th, but
drop back each time the button is pressed, then you have to differentiate
between a user entered and a modified date in that cell.

To have the 5th step back to the previous month, the formula becomes:

rg = rg - Day(rg - 5)

If you need to determine if the date which is the 5th was user entered or
machine modified, that's more complex, so post back.


--ron