Dates and ActiveX Calendar Controls

[JSearcy]

I have an ActiveX calendar (calendar 9.0) on a form that I have created for end users to enter data into an Excel database and a printable form. I need to be able to determine whether the date selected from the calendar is the first or third monday of the month and if not, give a message telling them that they must try again. (The message box is not a problem.) I know of several date functions and so on that work with VBA but cannot figure out how to get anything to work with the Calendar control... Any ideas??

Thanks in advance for any advice/help. I really appreciate it!

[C. O. Jones]

Put this at the start of your procedure that picks up the date selected from the calendar :-

Dim cd As Date, dte As Date, wd%, m%
cd = Calendar1.Value
dte = DateSerial(Year(cd), Month(cd), 1)
wd = Application.WorksheetFunction.Weekday(dte)
If wd > 2 Then
m = 7 - wd + 2
Else
m = 2 - wd
End If
If cd <> dte + m And cd <> dte + m + 14 Then
MsgBox "You must select either the first or third Monday of the month. Try again."
Exit Sub
End If


cojones

[JSearcy]

Thanks so much! That worked beautifully!MOST APPRECIATED!