David,

You need away around your error message:

Private Sub Calendar1_Click()
Dim StartDate As Date
If Weekday(Calendar1.Value) = 1 Then GoTo WrongDate
If Weekday(Calendar1.Value) = 7 Then GoTo WrongDate
If IsError(Application.Match(Calendar1.Value, Range("Holidays"), False))
_
Then GoTo WrongDate
Goto GoodDate
WrongDate:
MsgBox "You clicked a weekend or holiday", vbExclamation, Calendar1.Value &
_
" is not a working day"
Exit Sub
GoodDate:
StartDate = Calendar1.Value
End Sub

HTH,
Bernie
MS Excel MVP



"#DIV/0" wrote in message
...
I realised that just saying "not working for me" is a bit vague. So here's
the calendar click event.

Private Sub Calendar1_Click()
Dim StartDate As Date
If Weekday(Calendar1.Value) = 1 Then GoTo WrongDate
If Weekday(Calendar1.Value) = 7 Then GoTo WrongDate
If IsError(Application.Match(Calendar1.Value, Range("Holidays"),
False))
Then GoTo WrongDate
WrongDate:
MsgBox "You clicked a weekend or holiday", vbExclamation, Calendar1.Value
&
" is not a working day"
Exit Sub
StartDate = Calendar1.Value
End Sub

With just the Saturday/Sunday checks, the routine runs fine but after
putting in your holiday checker I get the warning message *every* day. We
have a local holiday in this Friday so I have a convenient date to click
for
verification but my VBA calendar is now telling me I should be on holiday
today and tomorrow too. Suits me but it's not what I was hoping for!


--
David M
WinXP - Office2003 (Italian)


"Bernie Deitrick" wrote:

Set up a named range "Holidays" with all your holiday and other 'office
closed' dates, then use

If IsError(Application.Match(Calendar1.Value, Range("Holidays"), False))
Then Msgbox "That's a
holiday!"

HTH,
Bernie
MS Excel MVP


"#DIV/0" wrote in message
...
How can I stop people selecting a listed company holiday from the
calendar
control ?
I already have a named range that I'm using for the weekday function
directly on the worksheet but I can't get the syntax for getting it to
work
from a userform.
For Weekends I already have Calendar1_Click do these checks
If WorksheetFunction.Weekday(Calendar1.Value) = 1
If WorksheetFunction.Weekday(Calendar1.Value) = 7
which open a msgbox "choose another date, stoopid - you'll be on
vacation
that day!" (or words to that effect).

Now I need a third line for the range that includes national holidays
and
other days the company will be closed.


--
David M
WinXP - Office2003 (Italian)