Solved: month end reminder

[ndendrinos]

Hello ,

I need a reminder that will show (on open) a MsgBox from the 27th to (and including) the 31st of each month. (that if possible could take care of Feb and all 30 days months)
Thank you

[JimmyTheHand]

[VBA]Private Sub Workbook_Open()
If Day(Date) >= 27 Then MsgBox "Month end is near."
End Sub[/VBA]

[ndendrinos]

Thank you JimmyTheHand
Will then this work?
Private Sub Workbook_Open()
If Day(Date) >= 27 Or =31 Then MsgBox "Month end is near."
End Sub

[Bob Phillips]

31 is >= 27, so it is totally superfluous.

[ndendrinos]

Yes of course it is.
Thank you

[mdmackillop]

31 is >= 27, so it is totally superfluous.

As is "totally"

[unmarkedhelicopter]

it is also very difficult for "=31" to be true ...

[ndendrinos]

I posted my first reply at 5AM . At 6AM while on my way to work I figured I was going to get it . Found MD's observation amusing .
Now to the helicopter : not sure I understand but assuming I would change my request and have the code run from the 27th to the 29th inclusive would one write it like this then ?

 
Private Sub Workbook_Open() 
If Day(Date) >= 27 Or =<30 Then MsgBox "Month end is near." 
End Sub

[OdiN]

I posted my first reply at 5AM . At 6AM while on my way to work I figured I was going to get it . Found MD's observation amusing .
Now to the helicopter : not sure I understand but assuming I would change my request and have the code run from the 27th to the 29th inclusive would one write it like this then ?

 
Private Sub Workbook_Open() 
If Day(Date) >= 27 Or =<30 Then MsgBox "Month end is near." 
End Sub

That would generate a message box on every day less than 30 and also on dates that are 31. Though you would write <=30 not =<30.

To display a warning on every day from 27-29 write this:

If Day(Date) >= 27 AND <=29 Then MsgBox "Month end is near."

[ndendrinos]

Thank you OdiN all noted
Regards to all
Nick

[Wolfgang]

Hi Bob,

Do you happen to know which version of Microsoft Excel Cicero was using to come up with a statement like that?!

Best,
Wolfgang

[ndendrinos]

 If Day(Date) >= 27 AND <=29 Then MsgBox "Month end is near."

Since all of you are in a great mood maybe you can teach me this:
I read above as: If today's date is equal or bigger that the 27th and smaller or equal to the 29th then ...
Why can't I express it like this then ? (just curious)

 If Day(Date) >26 Or <30 Then MsgBox "Month end is near."

[mdmackillop]

In the second case any numver from 1 - 29 satisfies the criteria of being < 30. Similarly 17 - 31 are >26, so you'll get messages for everything.
Try the following on a worksheet
=IF(OR(F5>26,F5<30),TRUE,FALSE)
and
=IF(AND(F5>=26,F5<=30),TRUE,FALSE)

[OdiN]

Okay you don't seem to be getting the logic of the OR/AND statements so I will attempt to explain.

For an OR statement you have this:

If (Condition 1) Or (Condition 2) Then

Do Code

End If

If either Condition 1 OR Condition 2 are true, the Do Code will run. So in your case you have this:

If Date > 26 Or Date < 30

All dates from 1-31 are either going to be > 26 or < 30, so the code will always run.


For an AND statement you have this:

If (Condition 1) AND (Condition 2) Then

Do Code

End If

Now this time in order for the Do Code to run BOTH Condition 1 AND Condition 2 must be true. In the OR example, only one of the conditions must be true in order for the Do Code to run.

So the statement If Date > 26 And Date < 30

That means that the Date must be between 26 and 30 for the conditions to both be true and the Do Code to run. 26 !> 26 and 30 !<30 so the code will not run if the date is the 26th or 30th.

In both cases, OR and AND - you can have more than 2 conditions, but it can get complex. You can also combine conditions. For Example:

If Date >=20 AND Date <=31 OR Date = 1 Then

Do Code

Else

Do Other Code

End If

Also you can do multiple conditions:

If (Condition 1) And (Condition 2) And (Condition 3) And (Condition 4) Then


Etc. etc.


I hope this makes things more clear.

[ndendrinos]

Sorry MD I ment it like this:

 If Day(Date) >26 And <30 Then MsgBox "Month end is near."

[Bob Phillips]

Hi Bob,

Do you happen to know which version of Microsoft Excel Cicero was using to come up with a statement like that?!

Best,
Wolfgang

Of course! I.VCI.XXVII, but it never took off, couldn't get the date function to work.

[OdiN]

Sorry MD I ment it like this:

 If Day(Date) >26 And <30 Then MsgBox "Month end is near."

That would work and it would run on 27, 28, and 29.

However, it's not easy to just look at it quickly and know when the conditions are met.

With >= 27 AND <=29 you can see right off the bat what date range you're looking for. It's the same thing really, just depends on if you're a Type A or Type B person I guess :P

[ndendrinos]

OdiN thank you for the explanation it's good of you to have taken the time.
So if I understand you my concept is correct but badly writen

If Day(Date) >26 And <30 Then MsgBox "Month end is near."

Here it means that if the date is the 27th . 28th or the 29th then the code runs.

[Bob Phillips]

[vba]

If Day(Date) > 26 And _
Day(Date) < Day(DateSerial(Year(Date), Month(Date) + 1, 0) - 1) Then
MsgBox "Month end is near."
End If
[/vba]

[OdiN]

I wouldn't say it's badly written, but I personally just wouldn't write it that way. If that works for you, then there you go