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.

    Yours,
    Chandoo
  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

    Hui...

  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

    Messages:
    5
    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.

    [​IMG]

    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), _
       DateSpecialEffect:=fmSpecialEffectRaised)
     
    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

    Messages:
    14,314
    Hi ,

    Thank you for sharing.

    Narayan
  3. Luke M

    Luke M Excel Ninja

    Messages:
    9,245
    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

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

    Ajesh Active Member

    Messages:
    100
    Cool Stuff. Thanks for sharing.
  6. inddon

    inddon Member

    Messages:
    338
    Thank you for sharing
  7. Asheesh

    Asheesh Excel Ninja

    Messages:
    1,097
    Nice one...!!
  8. schmolio007

    schmolio007 New Member

    Messages:
    5
    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

    Messages:
    1
    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?

Share This Page