Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exclude Sunday & Holidays | Excel Discussion (Misc queries) | |||
Enter date in userform using Calendar Control | Excel Discussion (Misc queries) | |||
Add days to a date, but exclude holidays | Excel Worksheet Functions | |||
Programmactically add calendar control to userform | Excel Programming | |||
How to use Microsoft Calendar Control 9.0 on a Userform | Excel Programming |