Message box after a date is selected

[jimbowvu80s]

I'm trying to have a message box popup if a user enters a date that is a Monday (or if they move off of the cell). Currently, I have a calendar that popus when the field is selected, but the user can bypass the calendar and enter the date manually.

I can't figure out how to pull Monday out of the date. The cell is currently formated to show March 19, 2009. Here's what I've been using.

Thank you

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "C3:E3" Then
Call OpenCalendar
End If

If Cells(3, 3).Value = vbMonday Then
MsgBox ("Today is Monday - Did you do a Safety Report")
End If


End Sub
---

 

[Yard]

Try

If WorksheetFunction.Weekday(Cells(3, 3), 2) = 1 Then
MsgBox ("Today is Monday - Did you do a Safety Report")
End If

 

[Yard]

Bearing in mind that message box will pop up every time the user moves cells, since the value in C3 will still be a MOnday. Maybe your Calendar popup belongs in the Worksheet_SelectionChange event, but maybe your Monday test belongs in the Worksheet_Change event?

 

[VoG]

Try

If Weekday(Cells(3, 3).Value) = 2 Then

 

[jimbowvu80s]

Thanks for the information, but that's kind of a bummer that it appears when you click on other fields when it's Monday. Will the worksheet_change event stop that? I do have a worksheet change already in that worksheet. This procedure checks for capitalization in a couple cells. Is it possible to have two Worksheet_Changes?

--partial code
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
On Error GoTo ErrHandler:

 

[VoG]

Is it possible to have two Worksheet_Changes?

No you can't. You'll need to combine the code into a single procedure.