Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default exclude holidays on UserForm Calendar control

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)
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,441
Default exclude holidays on UserForm Calendar control

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)



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default exclude holidays on UserForm Calendar control

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)


"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)




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default exclude holidays on UserForm Calendar control

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)




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,441
Default exclude holidays on UserForm Calendar control

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)






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Exclude Sunday & Holidays Butch Excel Discussion (Misc queries) 0 May 18th 09 09:57 PM
Enter date in userform using Calendar Control Scotty9349 Excel Discussion (Misc queries) 1 March 4th 09 08:02 PM
Add days to a date, but exclude holidays Chickadee Excel Worksheet Functions 13 July 31st 08 04:22 PM
Programmactically add calendar control to userform Greg Wilson Excel Programming 7 March 4th 06 12:18 AM
How to use Microsoft Calendar Control 9.0 on a Userform Anupam Sharma Excel Programming 4 August 27th 03 06:19 PM


All times are GMT +1. The time now is 03:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"