Date Macro

[one_more_cave_dweller]

I would like to trigger a macro if cell a1=the last day of any month. How could I write the code?

Thanks ray:

 

[Tom Urtis]

Since this is a formula based on TODAY then a Change event would not work.



See if this helps. Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.


Private Sub Worksheet_Calculate()
Dim MyDate As Date, dYr%, iMth%, iDay%, LastDay$
MyDate = Range("A1").Value
dYr = Year(MyDate)
iMth = Month(MyDate) + 1
iDay = Format(Day(MyDate))
LastDay = Format(DateSerial(dYr, iMth, 0), "dd")

If iDay = LastDay Then
MsgBox "A1's date is the last day of " & Format(iMth, "mmmm")

'your macro goes here

End If
End Sub

 

[Yogi Anand]

THANKS YOGI!!! That did it

Hi ... dweller:

Please see the comment by Tom Urtis and PA_HS_Teacher on the limitation of my approach; also see Yee388's fine contribution on use of the Worksheet_Change event.

 

[Tom Urtis]

Can someone, anyone, explain to me how a Change event solves this?

The question was how to trigger an event based on a condition in A1, which is a formula-containing cell. The formula in A1 is =TODAY()-1, which means it has no precedent cells because it returns a value based on the passage of time, not on any value entered or edited in a precedent or dependent cell.

A Calculation event such as I posted would seem to handle the event trigger, and while true that with a Calc event code in place, a change to a cell would trigger it, that was not even the case here because only a Change event was implemented.

This is especially curious because in the Change event that cave dweller says works, the target cell is A1 which is the cell containing the formula being evaluated.

So, how can that be and what am I missing? Maybe A1 really does not have a formula like cd said? What's up with this?

 

[one_more_cave_dweller]

Tom,

I'm really new to all this so all I can say for sure is that it works. It seems to me that the code is like an IF formula; If the value is false or not equal to the last day of the month then it exits. If the value is true a1 = the last day of the month the macro runs. Thats probably not correct but it makes sense to me, and it works.

Thanks to you and everyone that contributed to my question.

 

[Tom Urtis]

Thanks for following up, much appreciated. I will let this go after asking the question again about how a Change event for A1 can work for you in this case.

You wrote:
"I would like to trigger a macro if cell a1=the last day of any month..."

I asked:
"Depends on how that value got into A1. Was it returned from a formula in A1? Is it manually entered?..."

You answered:
"It is from a formula (Today()-1)"

To me, that means there is a formula in cell A1 which is =Today()-1

You received 3 suggestions. Two were the same in principal, they being a Change event for cell A1 only, meaning that an event would only be triggered if manual entry or edit took place in cell A1. If there really is a formula in cell A1, then any entry in A1 would destroy the formula.

The third suggestion was mine, which assumed based on your answer that there was a formula in A1, and so my code used a Calculation event to trigger an action based on when A1's formula returns the last day of a given month.

If there is not and never was a formula, then I don't understand your answer to my question about how the date got into A1 and you saying by a formula.

If there is a formula, then why allow a user to enter a new date in it, which would change the design of the spreadsheet.

The Change events you received would do the job based on manual entry of a date in A1, but that was not what you said you had...you said there was a formula in A1 so that is why I kept wondering what was really happening.

OK, thanks again for the post; I'll move off this but my curiosity got the better of me.

 

[one_more_cave_dweller]

Tom - I think I see the cause of your confusion. You wrote: "If there really is a formula in cell A1, then any entry in A1 would destroy the formula."
But the macro does not make an entry, it actually creates a form on another sheet and prints it. Cell a1 remains untouched, actually it is a locked cell so users cannot change the date.

Hope this helps.

 

[Tom Urtis]

I'm still shaking my head then, because if the users cannot change the date in A1 and cell A1 is locked (with the sheet presumably protected) then how can the Change event for that cell possibly take place. What you would have gotten is a message box saying the cell is protected if anyone tried to enter data in it. Still doesn't make sense but if you got what works for you, that's the important thing.