exclude holidays on UserForm Calendar control

[#DIV/0]

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.

 

[Bernie Deitrick]

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]

Hi Bernie,
Thanks for the suggestion but that's not working for me. I've tried removing
everything else from the Calendar1_Click sub, but still no luck.
I'd already tried a similar construction with VLookup and I think it may be
connected to the fact that Excel speaks Italian (all formulae are translated,
separators and sometimes syntax are different) and VBA only speaks US
English. I'll check on some Italian forums to see if there's a known problem
with calling Workbook functions from VBA. Although it copes with "weekday"
instead of "giorno.lavorativo" (which isn't true on worksheets or in
conditional formatting).

If anyone has alternative suggestions...

--
David M
WinXP - Office2003 (Italian)

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]

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)

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

 

[Bernie Deitrick]

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

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!