Date Navigator is invoked through a single function call, DateNavigator_QueryUser. Parameters passed to the function define how Date Navigator is displayed and operates. The function returns the selected date or the original date passed to it if the user cancels.
Syntax
Public Function DateNavigator_QueryUser( _ Optional ByVal Value As Variant, _ Optional ByVal MinDate As Variant, _ Optional ByVal MaxDate As Variant, _ Optional ByVal WorkDaysOfWeek As Variant, _ Optional ByVal Holidays As Variant, _ Optional ByVal SingleClickEnter As Variant, _ Optional ByVal StartUpPositionX As Variant, _ Optional ByVal StartUpPositionY As Variant, _ Optional ByVal TransparencyPercent As Variant, _ Optional ByVal IconColor As DateNavigator_IconColorEnum, _ Optional ByVal HeaderBackgroundColor As Variant _ ) As Variant
Parameters
Value [In]
Type: Variant (Date)
The current or starting date value. Optional. If omitted or zero then the current date is assumed.
MinDate [In]
Type: Variant (Date)
The minimum or earliest date that can be selected. Optional. If omitted then the earliest selectable date is January 1, 1900. Dates that cannot be selected are displayed in light grey.
MaxDate [In]
Type: Variant (Date)
The maximum or latest date to display. Optional. If omitted then the latest date that can be selected is December 31, 2099. Dates that cannot be selected are displayed in light grey.
WorkDaysOfWeek [In]
Type: Variant (single dimension array)
The work days of the week as a single dimension array of integer values from 1 to 7 where 1 is Sunday and 7 is Saturday. Optional. If omitted then the standard Monday through Friday are assumed.
Holidays [In]
Type: Variant (single dimension array)
A list of holidays as a single dimension array of dates. Optional. If omitted then no holidays are assumed.
HolidayNames [In]
Type: Variant (single dimension array)
A list of holiday names to display when the mouse hovers over a holiday. The list must match the holidays provided in the Holidays parameter (lower and upper bounds). If not then this parameter is ignored. Optional.
SingleClickEnter [In]
Type: Variant (Boolean)
True to close the user form when a date is clicked once, False to close it when a date is double clicked. Optional. If omitted then False is assumed.
StartupPositionX [In]
Type: Variant (Single)
The veritical position of the top of the user form. Optional. If both this and StartupPositionY are omitted then the top of the user form is set to the mouse position.
StartupPositionY [In]
Type: Variant (Single)
The horizontal position of the left side of the user form. Optional. If both this and StartupPositionX are omitted then the top of the user form is set to the mouse position.
TransparencyPercent [In]
Type: Variant (Integer or Long)
The user form transparency as a percent (0 to 80) where 0 is not transparent and 80 is very transparent. Optional. If omitted then 0 is assumed.
IconColor [In]
Type: DateNavigator_IconColorEnum
A DateNavigator_IconColorEnum value. The two supported colors are grey and blue. Optional. If omitted then grey is assumed.
HeaderBackgroundColor [In]
Type: Variant (Long)
A valid color value (RGB integer). Optional. If omitted then &H808080 (a medium grey – the same color as the grey controls) is assumed. To use the same color as the blue controls, use &H00E37961. [add description of how to use RGB function]
Return Value
Type: Date
If the user selects a date then the selected date is returned.
If the user cancels (presses ESC or escape) the operation then the original date passed in with the Value parameter is returned.
Calculating Holidays
Assuming an implementation in Excel, one method for providing holidays to Date Navigator is to maintain a list on a hidden worksheet and dynamically calculate the needed holidays around the current year. Assuming the current year is in cell A1 as an integer (e.g., 2017), the formulas for calculating various United States holidays are listed below. Note that these formulas do not calculate the actual date of the event, but rather the date of the observed holiday or non-work day.
Current Year
=YEAR(NOW())
New Year’s Day
=DATE(A1,1,1)+CHOOSE(WEEKDAY(DATE(A1,1,1)),1,0,0,0,0,0,-1)
Martin Luther King, Jr. Day
=DATE(A1,1,15)+7-WEEKDAY(DATE(A1,1,15)+5)
Presidents’ Day
=DATE(A1,2,15)+7-WEEKDAY(DATE(A1,2,15)+5)
Memorial Day
=DATE(A1,6,0)+1-WEEKDAY(DATE(A1,6,0)-1)
Independence Day
=DATE(A1,7,4)+CHOOSE(WEEKDAY(DATE(A1,7,4)),1,0,0,0,0,0,-1)
Labor Day
=DATE(A1,9,1)+7-WEEKDAY(DATE(A1,9,1)+5)
Columbus Day
=DATE(A1,10,1)+14-WEEKDAY(DATE(A1,10,1)+5)
Veterans Day
=DATE(A1,11,11)+CHOOSE(WEEKDAY(DATE(A1,11,11)),1,0,0,0,0,0,-1)
Thanksgiving Day
=DATE(A1,11,1)+(4-1)*7+7-WEEKDAY(DATE(A1,11,1)+(4-1)*7+2)
Day after Thanksgiving Day
=ThannksgivingDay+1
Christmas
=DATE(A1,12,25)+CHOOSE(WEEKDAY(DATE(A1,12,25)),1,0,0,0,0,0,-1)
Additional years can be added to the list by calculating each year off of the base year in A1. These holiday dates (and corresponding holiday names) can be passed to Date Navigator by referencing the range in which they are located and transposing them into a single dimension array:
Application.Transpose(Range("Holidays").Value)
Application.Transpose(Range("HolidayNames").Value)