Non-activex Datepicker Calendar Control

    All of you have been so helpful to me here over the years that I have been learning Excel and VBA. I finally feel like I can give something back to the community.


    Attached is a Non-ActiveX, Non MSCOMCT2.OCX dependent date picker calendar control for VBA userforms. To incorporate it into your project, you would need to copy the clsCalendar class module, and also add all the calendar control objects inside the FrameCalendar frame on the userform (including the frame itself) onto your form. You would also need another control on the form somewhere to be bound to the class, which would receive the user's selected date. Mine is called "TextBoxDate" and it is set in the UserForm code like this:


    Code
    Private Sub UserForm_Initialize()
    Set clsCal = New clsCalendar
    Set clsCal.Form(Me.TextBoxDate) = Me
End Sub


    Anyway, I hope it works for you, or at least it can give you ideas. I hope it's appropriate but I'm sharing this under terms of the "New BSD license," which is really flexible. I don't really mind what you do as long as no one takes this and sells it and pretends they came up with it.


    I developed this in 64-bit Excel 2010 beta, on 64-bit Windows 7.

    Re: Non-activex Datepicker Calendar Control


    well done... looks good. Thanks for sharing.


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Non-activex Datepicker Calendar Control


    I like what I see and I much prefer this to solutions where I need to have the PC have certain files resident in the Programs folder. Using your way, (I assume) I can create a spreadsheet, send it to someone else, and it will work on their PC without them having to anything with their PC to enable it?


    I may be asking too much here, and if this forum is only for the seriously advanced user, then I guess I am out of luck, but if you are willing, do you have step-by-step instructions for how to implement this solution?


    I got the sample file and like the look/feel. I would want to have the calendar pop-up whenever I selected, say, a Cell with a date formatted as d-mmm-yy (for example).


    Richard

    Re: Non-activex Datepicker Calendar Control


    Hmmm....


    This control depends on the functionality of VBA's userforms, so it really needs to be placed in a userform.


    In pre-Office 2007 versions of Excel, we could use a lot of tricks and APIs to remove a userform's title and borders, and make it look almost like it wasn't a userform, but it still needs to be a userform.


    I'm attaching a sample Office 2007-2010 macro-enabled workbook that demonstrates the behavior you desired. But it turns out to be a little complicated to implement, as you will see when you look through the code. You need 1) code in the worksheet itself to bring up the date picker, 2) a userform with the date picker controls on it, 3) the clsCalendar class module code, and 4) another normal module just to hold a public variable so that all the other code modules can access it.


    Unfortunately, this is a fairly advanced solution and it takes some understanding to suit it to your purposes. But I was once a beginner too, and I remember all the hours I spent trying to understand someone else's code. It was so helpful to me, I hope I can pass on the favor.


    Here's the sample workbook: forum.ozgrid.com/index.php?attachment/33067/

    Re: Non-activex Datepicker Calendar Control


    Bingo


    That is what I am looking for


    And by embedding it in the Sample Worksheet, you showed it can be sent to someone who can use it without setting up their system first - just enable macros.


    I'll look into this more on the weekend (I think it will take a while for me to grasp this) so thank you very much


    Richard

    Re: Non-activex Datepicker Calendar Control


    Baodad


    I was able to create a new Workbook and bring all your code/forms over to make this work


    thank you very much


    One question / ask :
    I see that the code in the "Microsoft Excel Object" is in the worksheet, not the workbook. This means I copy/paste the code for each worksheet I add to the workbook. I tried copying the code into the Workbook holder, but it did not work. Is there a simple change I could make to the code so I could put it into the Workbook holder and thereby have it available for any worksheet in the workbook?


    Great work and thanks again.


    Ramblin

    Re: Non-activex Datepicker Calendar Control


    Baodad,


    Is there a way to adapt the code so that instead of double-clicking on (any) cell, the pop-up (user) form only displays when you single-click on a cell that has been pre-formatted with a specific (date) format like d-mmm-yy ??


    Ramblin

    Re: Non-activex Datepicker Calendar Control


    Thanks Ramblin.

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Non-activex Datepicker Calendar Control


    The only reason to have code in a worksheet is to take advantage of the events that are available there. Same goes for workbooks. I'm attaching a screen shot that shows how you can browse through the events available to you depending on what object you've clicked on in "Microsoft Excel Object," shown in the tree in the left pane.


    [ATTACH=CONFIG]33290[/ATTACH]


    Once you click on an available event, the VBA editor will automatically insert the starting code you need. If there's an event that looks like it will work for you at the workbook level rather than the worksheet level, try it out. You can play with these events by putting simple code like

    Code
    msgbox "Event fired!"


    in one of the event subs it generates for you. Then go back to Excel and play around to see if you can make the code fire. (A message box should appear).


    If you find yourself needing to put duplicate code in many worksheets, the best practice is to remove as much common code as you can and put it in a code module. Then call that code from the worksheet event code.

    Re: Non-activex Datepicker Calendar Control


    Baodad


    You are great!


    After asking you a couple of questions about this, I discovered this post was in a forum with the instruction "Do not ask any questions". Well, you did answer, and guided me to a way to adapt this to have what I wanted. Thank you.


    I have attached, for you, and anyone else that wants to get the benefit of Baodad's good work (and my 2% adaptation), an Excel Workbook, with easy step-by-step instructions (for newbies like me) describing how to make this work in any other Excel 2010 Workbook.


    In the attached version, a couple of adaptations to Baodad's original:
    1) Instead of double-clicking any cell to bring up the Pop-Up Calendar, pre-format to "d-mmm-yy", using a Custom Format, any cell for which you want a pop-up calendar to appear when the cell is clicked on (single clicked).
    2) The code is set at the Workbook level, not the Worksheet level, so any additional sheets aded to the workbook will automatically have this capability as well.


    Thanks again to Baodad for solving a problem caused by Microsoft's change from Excel 2007 to 2010 which prevented the previous pop-up calendar that worked in Excel 2007 to crash in 2010.


    I have tested this and it works back to Excel 2003.


    Long live open source community!


    Ramblin

    Re: Non-activex Datepicker Calendar Control


    Great work!


    I can confirm that your workbook works in my Excel XP (2002) at home, even after file conversion from the .xlsm format.


    One regression in the code from the very first file I uploaded, is that the "v" character to the right of the year in the calendar should actually be rendered in the "Marlett" font, where it will be displayed as an up and down arrow. If you click the top half of the character, it increases the year, and vice versa.


    You can fix this by changing this property in the calendar class module:

    Re: Non-activex Datepicker Calendar Control


    Baodad,


    I am trying to decipher the code to adapt one thing, but it is not something I can readily get a handle on.


    Is it an easy fix to have the code set up such that if the cell being clicked on (which has the d-mmm-yy format) already has a date entered into it, the pop-up appears with that date already selected, instead of today's date? The pop-up should still come with today's date selected if there is no date in the cell being clicked on.


    RIchard

    Re: Non-activex Datepicker Calendar Control


    Yes, the code was designed to accept an initial date as part of the input. If you look at the "LoadView" sub in the calendar class module, you'll see that it accepts an optional date for an input parameter.


    LoadView is called from the code in the FormPicker form, here:

    Code
    Private Sub UserForm_Initialize()
    Set clsCal.BoundForm = Me
    clsCal.LoadView
End Sub


    The question is how to pass a (optional) date parameter to this code. Like,

    Code
    clsCal.LoadView <myDate>


    Off the top of my head, the first thing I'd try is adding another global variable (of Date type, since that's what LoadView accepts) to Module1. This would need to be checked and set (or cleared) each time the workbook event code was fired. Then that variable could be accessed and used in the LoadView call shown above.


    LoadView checks the parameter in this line:

    Code
    If dtDate = 0 Then dtDate = dtToday


    so if the date is set to zero LoadView will just start by loading the current month.

    Re: Non-activex Datepicker Calendar Control


    Got it


    Thanks for the help Baodad


    The attached file is like the others - complete with instructions - with the following update:


    When you click on a cell that is formatted with d-mmm-yy, the Pop-Up-Calendar will show (as before) but the year/month that is displayed will depend on what is already in the cell clicked on
    - if the cell is empty, just like now, the current month is displayed with Today's date highlighted
    - if the cell has a date already entered into it, the month/year of that date will be displayed
    with the date already entered into the cell highlighted.


    This was done by changing the Code in the FormPicker
    from

    Code
    Private Sub UserForm_Initialize()
    Set clsCal.BoundForm = Me
    clsCal.LoadView
End Sub

    to

    Code
    Private Sub UserForm_Initialize()
    Dim ExistDate As Date
    Set clsCal.BoundForm = Me
    If ActiveCell.Value = 0 Then
        clsCal.LoadView
    Else
        ExistDate = ActiveCell.Value
        clsCal.LoadView (ExistDate)
    End If
End Sub


    and changing the (numerous) if statements in the ClsCalendar LoadView Sub
    from

    Code
    If dtTemp = dtToday Then


    to

    Code
    If dtTemp = dtDate Then


    Thanks again Baodad for all your initial work and subsequent help!


    Ramblin

    Re: Non-activex Datepicker Calendar Control


    Hi all, nice work on this! very elegant solution. Just had a quick question, & I'm not sure if this a separate thread, but how easy/hard is it to convert/add international week feature. That is, having option where user can input Week/Year format for the date. Example, 01/10 = Week 1, 2010. More information on international week format here: http://blogs.msdn.com/b/excel/…eek-numbers-in-excel.aspx

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login