Macro on Mac OSX

[Michael Link]

Someone on another group graciously supplied this macro to me this
morning. It deposits the date and time into a cell and flashes up a message
box warning the user if it is after time X (in this case, 12 noon). My problem
is that, while the macro runs and deposits the time into the active cell just
great, the message box does not appear. (At the time I ran the tests, it was
already past noon.)

Since I'm running this in Excel X for mac, I thought perhaps someone here
might know of a mac issue that's keepiing this from working all the way.
Thank you!

Sub NewDateAndTime()

Dim mPrompt As String
Dim mBoxStyle As Long
Dim mTitle As String
Dim mMsg As Variant

mPrompt = "It's past 12:00 PM!"
mBoxStyle = 16 ' vbCritical
mTitle = "Warning!"

With ActiveCell

.Value = Now
.NumberFormat = "mm/dd/yy h:mm AM/PM"

If Now Mod 1 > 12 / 24 Then ' 12/24 = 12:00 PM
mMsg = MsgBox(mPrompt, mBoxStyle, mTitle)
.ClearContents
End If

End With

End Sub

 

[Daniel.M]

Hi Michael,

Instead of
If Now Mod 1 > 12 / 24 Then ' 12/24 = 12:00 PM
use:
If Hour(Now) >= 12 Then ' past noon

Regards,

Daniel M.

 

[Michael Link]

Thanks for the response. Actually, Excel crashed on me a while ago so I
can't try your suggestion now. I'll try tomorrow and let you know. Thanks!

 

[JE McGimpsey]

VBA's Mod() function is different from XL's MOD() function. From XL/VBA
Help:

The modulus, or remainder, operator divides number1 by number2 (rounding
floating-point numbers to integers) and returns only the remainder as result

Since floating point numbers are rounded to integers, X Mod 1 will
always return 0.

One way:


If Time > #12:00:00 PM# Then