New Excel Forum

This forum has been moved to TeachExcel.

Ask all future questions in the new excel forum.


Popup Calendar without MS DatePicker or ActiveX

Free Excel Macros
Forum rules
This forum is closed.

All new posts should be made at our new Excel Forum at

Popup Calendar without MS DatePicker or ActiveX

Postby Sisyphus » Thu Jan 02, 2014 6:07 am

I got tired of MS changing their calendar controls and decided I needed a DatePicker that will work without downloading any extra DLL's and registering them. I needed a DatePicker that can be used in any XLSM workbook - in fact, in any MS Office application - simply by installing its code. Such a tool is in the attached workbook. It works like a function (which, in fact, it is).
Code: Select all
    Dim MyDate As Date
    MyDate = GetDate

GetDate has three optional parameters
  • Prepick (As Variant)
    This is a pre-selected date which can be supplied as Date, as String or as Long integer. It is the date that will be pre-selected in the calendar when it opens. If this parameter is omitted the current date will be pre-selected.
  • Top and Left determine the positioning of the control on your screen
    The defaults are set to 50 each which positions the calendar in the center of your application screen, even in a multiple monitor environment.
    Please refer to the following link for a full description of the sub SetFormPosition.
    Code: Select all

Installation has been made easy.
Just copy the Function GetDate from the standard code module CalMan to any standard code module in your own VBA project. Drag the class module CalButton and the Form module DatePicker into your VBA project in the VBE's Project Explorer window - and that is all it takes.

At the top of the DatePicker code sheet you will find this enumeration. It is something like a control panel. It is the primary level of adjustment, intended and designed for the use of the uninitiated.
Code: Select all
    ' all dimensions in this form are related to the DefaultFontSize(NclDFS)
    ' enlarge or shrink the form by modifying this enum's value
    Enum Ncl                            ' Calendar
        NclDFS = 10                     ' points
        NclFirstDayOfWeek = vbMonday    ' specify the first day of the week
        NclFirstYear = 2000             ' can't be before 1904 [1901]
        NclFutureYears = 10             ' drop-down will list years from NclFirstYear
                                        ' until current year + NclFutureYears
        NclMargin = 6                   ' points
        NclColWidth = 24                ' points
        NclBackColor = 12648447         ' = Buff (&H00C0FFFF)
        NclButtonColor = 12648384       ' = Light green (&HC0FFC0)
        NclFontColor = 4210688          ' = Bluegreen (&H00404000)
        NclFontHiColor = 16711680       ' = Blue (&H00FF0000)
        NclHighlightColor = 8438015     ' = Ochre (&H0080C0FF)
    End Enum

As you see, you can adjust the size by assigning another font size to enum NclDFS. I have tried 4pt to 20 pt.
If your week starts with Saturday set NclFirstDayOfWeek = VbSaturday. You can set any day as the first day of the week.
You can determine which range of years will be shown in the Year-drop-down in the form.
Modifying NclMargin or NclColumnWidth would have major and untested repercussions on the appearance of the form. I can't recommend your doing that. But you can play with the colors if you don't like what I have done.

All controls are created on the fly. Therefore all their properties are set in the form's code sheet. If you wish to make more adjustments the procedure copied below will guide you to where you might try your hand.
Code: Select all
Private Sub UserForm_Initialize()
End Sub
Bear in mind that the Form's module only deals with the appearance of the form. All functionality is in the class module CalButton.

For testing purposes I have invented two possible applications, to insert a date in the next blank row of the worksheet, and to insert it at cursor point, i.e. in the currently selected cell. You can try these functions in the attached workbook. The relative code is in the code sheet of Sheet1(Calendar Test) (add date to new row) and the standard code module Sample_Code (Sub TestCalendar - assigned to the Shape in the worksheet). I have commented the code for these two procedures. Neither of them is required part of the tool. Either of them can be combined with code for adding a row at the bottom of a worksheet, inheriting formats and formulas but no values, if such a row requires a date. Please refer to this link for the relative code.
Code: Select all

The calendar code was written in VB7 / Excel 2010. If you need adaptation to other versions of VBA or Excel please send me a PM or ask a question on the VBA & Macros forum, telling me which part of the code - if any - throws an error in the version you run. It should be possible to fix. Apart from the testing procedures, the code contains nothing specific to Excel and should run equally well in the environment of any other MS Office application, including MS Access, MS Word, MS PowerPoint and others.

For the moment this code is brand new and may even have some faults or shortcomings I haven't discovered yet. If and when I do improved versions will be posted here. So, if you decide to use my calendar do check here occasionally to get whatever updates there might be.

Jan 3, 2014 - Parameter ReturnDefaultDate was removed
Jan 4, 2014 - Procedure SetFormPosition added / Code corrections (incl. removal of a major error in the test procedure)

Jan 5, 2014 - Selection drop-downs now displays 12 months / 6 years
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Former Moderator
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 2 guests