1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fully Customizable VBA Date Picker

Discussion in 'The Vault' started by schmolio007, Oct 3, 2014.

  1. schmolio007

    schmolio007 New Member

    I recently created a customizable, VBA-based date picker, and thought it might be of use to someone else. I know there are already a lot of custom date pickers out there to overcome the difficulties of using Microsoft's MonthView control. But I wanted one that could be customized to match the look and feel of whatever project it is being used in. I was never able to find one that could do this without significant code re-write.

    You can click here to download the custom date picker I came up with. Below are some screenshots of the form in action.


    To use the date picker, simply import the CalendarForm.frm file into your VBA project. Each of the calendars above can be obtained with one single function call. The result just depends on the arguments you use (all of which are optional), so you can customize it as much or as little as you want.

    For example, the most basic calendar on the left can be obtained by the following line of code:

    Code (vb):
    MyDateVariable = CalendarForm.GetDate
    That's all there is to it. From there, you just include whichever arguments you want to get the calendar you want. The function call below will generate the green calendar on the right:

    Code (vb):

    MyDateVariable = CalendarForm.GetDate( _
       SelectedDate:=Date, _
       DateFontSize:=11, _
       TodayButton:=True, _
       BackgroundColor:=RGB(242,248,238), _
       HeaderColor:=RGB(84,130,53), _
       HeaderFontColor:=RGB(255,255,255), _
       SubHeaderColor:=RGB(226,239,218), _
       SubHeaderFontColor:=RGB(55,86,35), _
       DateColor:=RGB(242,248,238), _
       DateFontColor:=RGB(55,86,35), _
       SaturdayFontColor:=RGB(55,86,35), _
       SundayFontColor:=RGB(55,86,35), _
       TrailingMonthFontColor:=RGB(106,163,67), _
       DateHoverColor:=RGB(198,224,180), _
       DateSelectedColor:=RGB(169,208,142), _
       TodayFontColor:=RGB(255,0,0), _
    Here is a small taste of some of the features it includes. All options are fully documented in the userform module itself:
    • Ease of use. The userform is completely self-contained, and can be imported into any VBA project and used without much, if any additional coding.
    • Simple, attractive design.
    • Fully customizable functionality, size, and color scheme
    • Limit user selection to a specific date range
    • Choose any day for the first day of the week
    • Include week numbers, and support for ISO standard
    • Clicking the month or year label in the header reveals selectable comboboxes
    • Dates change color when you mouse over them
    Deepak, Khalid NGO, JAMIR and 4 others like this.
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Hi ,

    Thank you for sharing.

  3. Luke M

    Luke M Excel Ninja

    Very cool, thanks for sharing. I'll move this to our Vault of Deep, Dark Secrets. Ok, maybe it's just called the Vault. But it's still where we keep all sorts of cool tricks/gems. :):awesome:
  4. Abdul Matheen

    Abdul Matheen Member

    great stuff, really very useful. Thanks for sharing.
  5. Ajesh

    Ajesh Active Member

    Cool Stuff. Thanks for sharing.
  6. inddon

    inddon Member

    Thank you for sharing
  7. Asheesh

    Asheesh Excel Ninja

    Nice one...!!
  8. schmolio007

    schmolio007 New Member

    Thanks, everyone! Glad you like it.

    One thing I am curious about is if the calendar holds up okay with different date formatting tendencies internationally. I've never dealt with distributing a spreadsheet outside the United States, so that was something I didn't really know how to account for.

    Anyone experience any issues? Or anything you would do differently to make it more compatible with different use cases?
  9. Pickles

    Pickles New Member

    Hi schmolio007 - This worked great in my Excel form except users were complaining they could not open any other workbooks in Excel while my form was running. I fixed this by changing the ShowModal property of my userform to False. However when I now invoke the calendar form, it no longer returns the value to the target field in my userform and jumps to another element of the form. Is there any way of solving this so I can still use your calendarform and allow users to open other Excel workbooks while my form is open?
  10. Taxgirl

    Taxgirl New Member

    I'm not able to download the userform like it says in the instructions. Can you please email me the file? This would be the icing on my project to have this date picker feature!
  11. SirJB7

    SirJB7 Excel Rōnin

  12. Taxgirl

    Taxgirl New Member

    Dropbox says that the file has moved. When I go to your website and click download, an excel file downloads, but there is no zip file as mentioned in your instructions on the website. Am I missing something obvious? Here are the instructions I am referring to:

    Start by clicking the link above to download CalendarForm v1.5.2.zip. Extract the files in the zip archive, and save the CalendarForm.frm and CalendarForm.frx files on your computer.

    Thanks again for your help!
  13. SirJB7

    SirJB7 Excel Rōnin

  14. Taxgirl

    Taxgirl New Member

    Yes, I can get to this website, but as I said before a macro enabled spreadsheet downloads, not a zip file. I am looking for the zip file with the CalendarForm.frm and CalendarForm.frx files. I can provide my email address if it is easier to email these items. Are these files located within the excel spreadsheet that downloads? Please help!
  15. SirJB7

    SirJB7 Excel Rōnin

    Hi, Taxgirl!
    The file has a userform named CalendarForm. And in the instructions the author asks to email him if any issue. And in the same place you can find his email.
  16. NARAYANK991

    NARAYANK991 Excel Ninja

    Hi ,

    The workbook named CalendarForm v1.5.2.xlsm has a userform (with its associated code) and a code module.

    Once you open this workbook , go to the Visual Basic Editor.

    Right-click on the CalendarForm , and select Export File ; this will save a file named CalendarForm.frm in the location you specify.

    Right-click on the Module1 , and select Export File ; this will save a file named Module1.bas in the location you specify.

    When ever you want to use this application , you will have to import both these saved files into your workbook ; thereafter , you can run or call any of the following 3 procedures :




    Note that the selected date will be stored in pre-defined worksheet cells H16 , H34 or H61 depending on which procedure you run or call.

  17. Lis

    Lis New Member

    Thanks Narayan! Helped heaps :)

    Can anyone tell me how to link this datepicker to an input cell on a userform?
    I am only a novice at vba so simple is good!

    (I have 10 textboxes and the first is a date. I would like the user to select the date using the datepicker, and when the other boxes are filled, have the date transferred to the worksheet with the other text info. I have working code for transferring the text, just don't know how to get datepicker info onto the form and into the worksheet)

    Thank you!

Share This Page