Introduction

Introducing DatePicker for Excel

Flexible Date Restriction with Single Date Selection from a UserForm Popup Calendar in a Worksheet or from a Textbox on a UserForm for Excel 2016 (32bit or 64bit, Version 16)

DatePicker is my latest Draggable, Popup Calendar-style Dialog allowing you to select a single Date and return it to the Cell built using a UserForm. DatePicker is similar to my Popup Calendar Software but doesn't use an embedded image to restyle the Dialog, is more compact and has a different look and feel to it. You can toggle the Months and restrict the Date range to a specific consecutive Day range (ie. Monday to Sunday for the current week), a single Month (October 2021) or a specific consecutive Week range (next 2 weeks). You can use my DatePicker with a UserForm to return a Date to a Textbox control. You can dismiss the DatePicker using the Escape {ESC} key. You can position the DatePicker right next to the ActiveCell in a Worksheet and drag the DatePicker around using the left Mouse Button. DatePicker allows for very flexible Date restrictions ie. allow Mondays only to be Booked for the next 4 Years. There are toggle arrows for the Years and you can left/right-click on the Header to increment the Months


     

If you purchase this Project, I will include the following Excel Software in the download for FREE!


Popup Calendar

Prerequisites

  • Microsoft Excel 2016 Version 16 (32bit or 64bit) · designed for Office 365
  • Basic to moderate Excel Skills and some VBA skills to import Code Modules into own Project if not using the purchased Spreadsheet
  • Windows PC · NOT a Mac!
  • No support is provided for customization of this Software

Features

  • FadeIn and FadeOut the DatePicker
  • Draggable UI Interface - move the DatePicker around with the Mouse
  • Browse previous and next Calendar Months using the tiny left/right arrows at the bottom of the DatePicker
  • Move up and down through the Calendar years using the tiny up/down arrows at the bottom of the DatePicker
  • Use as Modal or Modeless DatePicker
  • Hover highlighting effect on the Dates
  • Use the DatePicker with a UserForm
  • Press {ESC} while using the DatePicker to dismiss the Dialog
  • Mouse Click anywhere on the Worksheet to dismiss the Calendar Dialog
  • DatePicker positioning - use horizontal and vertical adjustments to position the DatePicker next to the Cell
  • Restrict the Date Range of the Calendar ie. between 2 Months or >= Current Month etc.
  • Pick a Date for one Cell and then another for another Cell in one operation - see Example 6
  • Choose a different Theme for the Colour of the DatePicker - see Examples 10 & 11
  • Apply a specific Month Format to the Calendar Title for the Month and Year when it is displayed
  • Apply a specific Cell Date Format whenever a Calendar Date is picked
  • Change the Border Colour of the IDialog UserForm
  • DatePicker automatically closes whenever a user selects another Worksheet in the Workbook
  • Use Left & Right Mouse clicks on the Placeholder Header for the Title to Increment/Decrement the Months
  • Restrict specific Dates on the Calendar ie. the Monday until the Sunday for the current Week based upon Todays Date - see Example 12
  • Restrict Single Day Dates on the Calendar for a specific interval ie. "01|12|18" - see Example 15, I have allowed the Single Day Dates 1st, 12th and 18th of any Month to be picked from the current Month until the next 6 Months only
  • Restrict multiple whole Dates - see Example 16, I have allowed the Multiple Dates 08/10/2021, 15/10/2021, 22/10/2021, 19/11/2021 and 24/12/2021 only (nothing else can be Selected on the DatePicker)
  • Restrict every Monday Days on the Calendar for the next 4 Years - see Example 17, Here we will restrict every Monday for Bookings between 2021 and 2024
  • The Download includes an examples Sheet containing demonstrations of how to use the DatePicker
  • As it is a small resource, the Download Zip File contains all of the Versions of the DatePicker so far and the Class and Code Modules for the latest Version for direct Import into Excel
  • The Download includes my Popup Calendar Software FREE of charge







Installation

Unzip the DatePicker.zip File and open DatePicker.xlsm or the latest File ie. DatePicker-v1.1.xlsm (multiple Files are included in the Zip File as the resouce is small, so you will have a bunch of Files to play around with)

Test the examples to see what DatePicker can do. If you want, you can now start your own Project using the File or you can start from scratch by importing the Code Modules which are in the zip archive

Notice that I have also included the Popup-Calendar files as well - purchasing DatePicker gives you both Projects!

Usage

How to use DatePicker


Examples
On this Sheet you will find all of the DatePicker examples. The VBA Code used for all of these examples is in the Examples, Worksheet Code Module

The following examples are available:
1. Modeless - trigger from an Icon.  Start Date for current Month.  Select Date, click anywhere or press {Esc} to dismiss the DatePicker
2. Modeless - trigger from an Icon.  Start Date for next Month.  Select Date, click anywhere or press {Esc} to dismiss the DatePicker. Year change arrows are switched on
3. Modeless - trigger from a single Cell selection by the Mouse.  Select Date, click anywhere or press {Esc} to dismiss the DatePicker
4. Model - trigger from a single Cell selection by the Mouse.  Select Date or press {Esc} to dismiss the DatePicker. Year change arrows are switched on
5. Modeless - trigger from a single Cell selection by the Mouse and set the Month/Year Date if it already exists in the Cell.  Select Date, click anywhere or press {Esc} to dismiss the DatePicker
6. Select between 2 Dates
7. Restrict Date Range to October 2021 and output a reformatted Date ie. 8-Oct (dd-mmm)
8. Restrict Date Range to anything >= current Month and output a reformatted Date
9. Restrict Date Range up until the end of next Month and output a reformatted Date
10. Alternative Blue Colour Theme. Year change arrows are switched on
11. Alternative Slate Colour Theme. Year change arrows are switched on
12. Restrict Date from the Monday of the current Week until the Sunday of the current Week depending on Todays Date. Year change arrows are switched on
13. UserForm Example. Click the Icon to bring up a dummy UserForm and see how you can use it for your UserForms.  You can set the UserForm itself to Modal or Modeless.  The demonstration shown then has the DatePicker set to a Modal DatePicker
14. Alternative Burnt Orange Colour Theme. Year change arrows are switched on.  This example also changes the Idialog.BorderColor to Burnt Orange

Version 1.1
15. Demonstrating the use of Single Day Dates to restrict the Calendar.  I have allowed the Single Day Dates 1st, 12th and 18th of any Month to be picked from the current Month until the next 6 Months only
16. Demonstrating the use of Multiple Dates to restrict the Calendar.  I have allowed the Multiple Dates 08/10/2021, 15/10/2021, 22/10/2021, 19/11/2021 and 24/12/2021 only - Nothing else will be allowed, period!
17. Here we will restrict every Monday for Bookings between 2021 and 2024.  This is pretty cool and gives you a lot of lexibility when allowing users to make Bookings using your DatePicker!
18. Restrict by a list of Dates in a Worksheet

Calling the DatePicker
To bring up a Modeless DatePicker Calendar from a click on an Icon, Button or Shape Object, link a Macro ie. Example1() to your Icon (or Object) and then use the following Code:
Public Sub Example1()
   ThisWorkbook.ActiveSheet.Range("B10").Activate
   DatePicker.Init FadeIn:=True, FadeOut:=True, FadeSpeed:=12, FormatActiveCell:="dd/mm/yyyy"
   DatePicker.Popup LeftAdjustment:=5, TopAdjustment:=5, DismissByEscape:=True, ModalDialog:=False
End Sub
To bring up a Modeless DatePicker Calendar from a click in a specific Cell use the following Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Application.Intersect(Me.Range("B10"), Range(Target.Address)) Is Nothing Then
      If Target.CountLarge = 1 Then Example1
   Else
      DatePicker.Running = False
      DatePicker.StartDate = 0
      Erase cls
      Unload IDialog
   End If
End Sub

Public Sub Example1()
   ThisWorkbook.ActiveSheet.Range("B10").Activate
   DatePicker.Init FadeIn:=True, FadeOut:=True, FadeSpeed:=12, FormatActiveCell:="dd/mm/yyyy"
   DatePicker.Popup LeftAdjustment:=5, TopAdjustment:=5, DismissByEscape:=True, ModalDialog:=False
End Sub

Parameters
You can pass the following Optional Parameters for the DatePicker.Init() subroutine. FadeIn/FadeOut are boolean values either True or False. FadeSpeed is a number, the larger the number the quicker the fade. Month Format is the Date format shown at the top of the Popup Dialog passed as a String. Format Active Cell is the Date format of the Cell where the Date is returned to passed as a String. Theme is an allowed Enum (or long) Colour Theme that you can maintain in the DatePicker Code Module - there are 3 available enumerations, Default, Blue and Slate. DateFrom and DateTo are Optional Dates in a variety of formats that you can set to restrict the Dates allowed on the DatePicker Calendar - they must be passed as Date specific but you can use the DateSerial function as well. AllowYearChange renders an additional 2 arrows that can be used to move up or down through the Calendar years - it is set to False by default. AllowUseOnUserForm should only be set to True when you are using the DatePicker on a UserForm. AllowQuickMonthChange set to True will let you quickly increment and decrement the Months on the DatePicker by clicking anywher in the header
DatePicker.Init FadeIn:=True, FadeOut:=True, FadeSpeed:=12, MonthFormat:="mmm yy", FormatActiveCell:="dd/mm/yyyy", Theme:=Slate, DateFrom:=Date - Weekday(Date, 2) + 1, DateTo:=Date - Weekday(Date, 2) + 7, AllowYearChange:=True, AllowUseOnUserForm:=False, AllowQuickMonthChange:=True

You can pass the following Optional Parameters for the DatePicker.Popup() subroutine. Left Adjustment is the adjustment to position the Popup Dialog horizontally next to the Cell. Top Adjustment is the adjustment to position the Popup Dialog vertically next to the Cell - both positive and negative values are allowed. Dismiss By Escape allows the Popup Dialog to be dismissed by pressing the Escape {ESC} Button. Modal Dialog can be True for a Modal Dialog or False for a Modeless Dialog (Modeless means that you can select another Cell in the Worksheet to dismiss the Dialog or to populate it with a Date if you want - this will require a slight Code change to the way that you use the DatePicker)
DatePicker.Popup LeftAdjustment:=5, TopAdjustment:=5, DismissByEscape:=True, ModalDialog:=False

Setting the initial Date for the DatePicker
There may be times when you want to set the Month Date that you want to display by default when you launch the DatePicker when a user clicks into a Cell that may or may not already contain a Date. This can be done by setting the extended DatePicker Property like this (wrapped using an error skip just in case text or some other format currently exist in the Cell which is less complicated than checing for any kind of valid Date):
On Error Resume Next
If ActiveCell.Value2 <> vbNullString Then DatePicker.StartDate = DateSerial(Year(ActiveCell.Value), Month(ActiveCell.Value2), 1)
On Error GoTo 0

Using Multiple DatePickers to return Dates for 2 Cells
There may be a time that you want to use the DatePicker to return Dates for multiple Cells ie. a Start Date and then an End Date for some reason. To do this you can use Code like this (notice the aditional wrapping of the checks for the DatePicker Property DatePicker.Running and the pause loop as we wait for the DatePicker UserForm to become invisible meaning that it has just closed):
   If DatePicker.Running = False Then
      ThisWorkbook.ActiveSheet.Range("B30").Activate
      DatePicker.Init FadeIn:=True, FadeOut:=True, FadeSpeed:=12, FormatActiveCell:="dd/mm/yyyy"
      DatePicker.Popup LeftAdjustment:=5, TopAdjustment:=5, DismissByEscape:=True, ModalDialog:=False
   End If
   Do While IDialog.Visible = True
      DoEvents
   Loop
   If DatePicker.Running = False Then
      ThisWorkbook.ActiveSheet.Range("D30").Activate
      DatePicker.Init FadeIn:=True, FadeOut:=True, FadeSpeed:=12, FormatActiveCell:="dd/mm/yyyy"
      DatePicker.Popup LeftAdjustment:=5, TopAdjustment:=5, DismissByEscape:=True, ModalDialog:=False
   End If

Using an Alternative Theme
3 Themes are added as an Enum at the top of the DatePicker Code Module to change the look and feel of the DatePicker Colours. The Header or Titlebar and the Hover Colours are both changed whenever a Theme is changed. Current Themes are Default (Green), Blue and Slate. You can change the Theme for the DatePicker to Slate (or Carbon effect) Colours like this (you can easily build up any types of Themes that you want for the DatePicker - the Enum can be VBHex or Long variable data types):
   DatePicker.Init FadeIn:=True, FadeOut:=True, FadeSpeed:=12, FormatActiveCell:="dd/mm/yyyy", Theme:=Slate
   DatePicker.Popup LeftAdjustment:=5, TopAdjustment:=5, DismissByEscape:=True, ModalDialog:=False

Restricting the Date Ranges on the DatePicker Calendar
There may be times when you want to ask a user for a Date whereby to avoid additional checking using Code, you would like to restrict the DatePicker Calendar so that the only Dates a user can select are ones that you want back. For example, suppose you are only concerned with Dates in the current week. To force a user to return either Blank (the DatePicker was cancelled) or a Date that falls from a Monday to a Sunday in the current week you can use Code like this (notice that I use variables to derive the start and end Dates prior to passing them in the DatePicker.Init() Parameters - all Dates falling outside of these will not be enabled on the Calendar):
  Dim StartOfWeek As Date
  Dim EndOfWeek As Date
  StartOfWeek = Date - Weekday(Date, 2) + 1
  EndOfWeek = Date - Weekday(Date, 2) + 7
  ThisWorkbook.ActiveSheet.Range("B54").Activate
  DatePicker.Init FadeIn:=True, FadeOut:=True, FadeSpeed:=12, FormatActiveCell:="dd-mmm", Theme:=Slate, DateFrom:=StartOfWeek, DateTo:=EndOfWeek, AllowYearChange:=True
  DatePicker.Popup LeftAdjustment:=5, TopAdjustment:=5, DismissByEscape:=True, ModalDialog:=False

Allowing only Mondays to be Booked using the DatePicker for the next 4 Years
DatePicker is very flexible in allowing how you let a user Select Dates from the Popup Calendar. From Version 1.1 I introduced Date Selections in the form of a String variable that can hold either single Dates ie. "01|05|19" meaning only allow the 1st, 5th or 19th Day of any Month or "01/10/2021|19/11/2021" meaning only allow 2 Dates to be Selected either 1st October 2021 or 19th November 2021. This functionality and the existing Date range functionality gives you pretty flexible Date restriction. Here I will restrict every Monday for Bookings between the current Year, 2021 and 2024. We do this by populating a String with every Monday for the 4 Year horizon. Whenever you use the DateSelections functionality you also have to set the DateFrom and the DateTo Dates, in the .Init() Method, but you can specify the full Date Horizon using the example DateSerials() below:
Private Sub Example17()
   ThisWorkbook.ActiveSheet.Range("B81").Activate
   
   ' let's fill a Pipe-delimited String with every Monday for 4 Years
   Dim ADate As Date
   Dim MyDates As String
   ADate = DateSerial(2021, 1, 1)
   If Weekday(ADate) = vbMonday Then MyDates = Format$(ADate, "dd/mm/yyyy")
   Do While Year(ADate) < 2025
      ADate = ADate + 1
      If Weekday(ADate) = vbMonday Then
         MyDates = MyDates & "|" & Format$(ADate, "dd/mm/yyyy")
      End If
   Loop
   
   DatePicker.UseDateSelections = True
   DatePicker.DateSelectionType = WholeDates
   DatePicker.DateSelections = MyDates

   DatePicker.Init FadeIn:=True, FadeOut:=True, FadeSpeed:=12, FormatActiveCell:="dd/mm/yyyy", AllowYearChange:=True, Theme:=BurntOrange, AllowQuickMonthChange:=True, DateFrom:=DateSerial(1900, 1, 1), DateTo:=DateSerial(9999, 1, 1)
   DatePicker.Popup LeftAdjustment:=5, TopAdjustment:=5, DismissByEscape:=True, ModalDialog:=False
End Sub

Using the DatePicker on a UserForm
You may want to use the DatePicker on a UserForm to select a Date that can be returned to a Textbox Control. To do this you must set AllowUseOnUserForm to True in the DatePicker.Init() subroutine which tells the Code how to handle the DatePicker positioning and add some small additional Code to your UserForm before you launch it, for example, using 'UserForm1.Show'. The main 2 Property's that are required to be added are DatePicker.UserFormID which should be any valid UserForm Object or you can use the 'Me' tag which implies the active UserForm Object and DatePicker.TextBoxID which should be any valid Textbox Control Name (a String not the Control itself) like this (you can add these to specific Button or when the UserForm is Activated - then just use any standard call to the DatePicker subroutines to display it):
   ' required Properties!
   DatePicker.UserFormID = Me
   DatePicker.TextBoxID = Me.TextBox1.Name
   
   DatePicker.Init FadeIn:=True, FadeOut:=True, FadeSpeed:=12, FormatActiveCell:="dd/mm/yyyy", AllowUseOnUserForm:=True, AllowYearChange:=True
   DatePicker.Popup LeftAdjustment:=10, TopAdjustment:=50, DismissByEscape:=True, ModalDialog:=True

Protecting the Icons or Drawing Objects
To protect any Icons from user-interference or accidental right-clicks and movement you can use a subroutine like this: (run when you have finished coding - use Sheet->Unprotect to remove it and allow editing or movement again)
Private Sub ProtectDrawingObjectsInActiveSheet()
   ActiveSheet.EnableSelection = xlNoRestrictions
   ActiveSheet.Protect Contents:=False, UserInterfaceOnly:=True
End Sub

Changing the Border Colour
You can change the Colour of the IDialog Border before a call to either the DatePicker.Init() or DatePicker.Popup() subroutines like this:
IDialog.BorderColor = BurntOrange

Screen Shots

DatePicker Version 1.1 with a slightly darker Font Colour for the Weekend Dates

A Screenshot of the Examples Sheet for the DatePicker

A Screenshot of an Alternative Blue Colour Theme for DatePicker

A Screenshot of an Alternative Slate Colour Theme for DatePicker

A Screenshot of the DatePicker as I am about to increment the Year to 2022

A Screenshot of an Alternative Slate Colour Theme for DatePicker including the UserForm Border Colour and with the Year increment/decrement Arrows switched on

A Screenshot of the DatePicker being used together with a UserForm

A Screenshot of an Alternative Burnt Orange Colour Theme for DatePicker including a Burnt Orange IDialog Border Colour

DatePicker allowing a user to ONLY Book Mondays for the next 4 Years

FAQ

Q. Can I set a different Date when I display the DatePicker?
A. Of course. You can set the DatePicker.StartDate Property using the DateSerial function like this:
DatePicker.StartDate = DateSerial(Year(Date), Month(Date) + 1, 1) for the next Month or like this:
DatePicker.StartDate = DateSerial(2023, 2, 1) for the first of February 2023


Q. Can I set the DatePicker to allow only the first Day of any Month to be selected?
A. Yes from Version 1.1 you can do exactly this! To do this set: DatePicker.UseDateSelections = True, DatePicker.DateSelectionType = SingleDayDates and DatePicker.DateSelections = "01" and then set the DateFrom and the DateTo in the .Init() Method to DateFrom:=DateSerial(1900, 1, 1), DateTo:=DateSerial(9999, 1, 1) - this will allow the full Date range from the start of Excel Date calculation until the end and only allow the 1st of every Month to be Booked


Videos

This is the first video of the DatePicker in action. In this demonstration, I go through each of the examples on the Examples Sheet in the DatePicker.xlsm File up until Example 5. Music Evil Empire (Original Mix) by Tycoos Get it Here · view this DatePicker video on YouTube

This is the second video of the DatePicker in action. In this demonstration, I go through each of the examples on the Examples Sheet in the DatePicker.xlsm File from Example 5 onwards. Music Last Night In The Sky by Shake Get it Here · view this DatePicker video on YouTube

Support

Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support (limited) for my Software includes

  • Responding to questions or problems regarding the Software and its features
  • Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN

Software support does not include

  • Customization and installation services
  • Support for third party software or ANY kind of development whatsoever

Before seeking support

  • Make sure your question is a valid Software Issue and not a customization request
  • Make sure you have read through the FAQ's, online documentation and any related video guides before asking support on how to accomplish a task
  • Ensure that you access to the VBOM is allowed and that Macros can run in Excel
  • Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook

How to get Support

Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order Number
...or Contact Mark Kubiszyn via our Facebook Page - remember to be patient, if there has been an issue with your download, Mark will always respond within 48 hours and will Email you the File directly if neccessary or via Messenger. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked

Future Builds

* No ideas as yet!

Bug Fixes

* There are currently no bugs identified for DatePicker

Changelog

Read more information on the status of each release below. The latest Version including a description of any changes is shown first

11.10.2021 - (Version 1.1)

A number of tweaks to allow a more flexible, extensive range of Dates to be Selected on the Calendar - see Examples 15 through to 18
15. Demonstrating the use of Single Day Dates to restrict the Calendar
16. Demonstrating the use of Multiple Dates to restrict the Calendar
17. Here we will restrict every Monday for Bookings between 2021 and 2024
18. Restrict by a list of Dates in a Worksheet

Changed the default Font Forecolour of the Weekend Days to a slightly darker Colour so that when the Dates are not enabled, you can clearly tell what you are allowed to select if the Day falls on a Weekend

02.10.2021 - (Version 1 tweaks)

1. Add the ability for the DatePicker to automatically close whenever a user selects another Worksheet in the Workbook
2. Added in another demo to illustrate how to select two Dates
3. Added in the ability to restrict the Date Range on the Calendar ie. only allow users to select a Date within October 2021
4. Added the ability to pick different Themes for the DatePicker, currently restricted to Colours ie. Default Theme is Green, Blue is a Blue theme
5. Removed some of the 'If Not DatePicker.Running Then' statements as they are only required if Date restrictions are used
6. Added in the ability to restrict specific Dates on the Calendar ie. the Monday until the Sunday for the current Week based upon Todays Date
7. Added in the ability to use Left & Right Mouse clicks on the Placeholder Header for the Title to Increment/Decrement the Months

01.10.2021

General Release