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:
    6
    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
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,830
    Hi ,

    Thank you for sharing.

    Narayan
  3. Luke M

    Luke M Excel Ninja

    Messages:
    9,301
    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:
    104
    Cool Stuff. Thanks for sharing.
  6. inddon

    inddon Member

    Messages:
    339
    Thank you for sharing
  7. Asheesh

    Asheesh Excel Ninja

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

    schmolio007 New Member

    Messages:
    6
    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?
  10. Taxgirl

    Taxgirl New Member

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

    Messages:
    8,894
  12. Taxgirl

    Taxgirl New Member

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

    Messages:
    8,894
    Thomas Kuriakose likes this.
  14. Taxgirl

    Taxgirl New Member

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

    Messages:
    8,894
    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.
    Regards!
  16. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,830
    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 :

    BasicCalendar

    AdvancedCalendar

    AdvancedCalendar2

    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.

    Narayan
    Thomas Kuriakose likes this.
  17. Lis

    Lis New Member

    Messages:
    2
    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!
  18. syd1

    syd1 New Member

    Messages:
    3
    Hello all,

    I have a problem to open this file in both excel 2007 and 2010. Does anyone experience this? Any idea what is wrong?

    Thanks
    syd
  19. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,830
    Hi ,

    What is the problem ? Is any error message displayed ?

    Are you sure the downloaded file is not corrupted ?

    Can you upload the file you have downloaded , so that we can try opening the same file and see whether we face any problems ?

    Narayan
  20. syd1

    syd1 New Member

    Messages:
    3
    Hello Narayan,

    thanks for reply.
    I have uploaded screen of two messages. I tried another download (3rd time) but still the same.

    Attached Files:

  21. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,830
    Hi ,

    I could not open the file you uploaded.

    I am uploading the file I downloaded from the author's website. See if you face the same problem with this.

    Narayan

    Attached Files:

  22. syd1

    syd1 New Member

    Messages:
    3
    Hi Narayan,

    many thanks. Your uploaded file works well for me. Don't understand why my files were corrupted (I Also tried to download and open from different PC with the same situation).

    Regards,
    syd
  23. macdonaldj

    macdonaldj New Member

    Messages:
    2
    Hi. Great looking calendar !
    Is there any way that it could be added onto another form - like the MonthView control itself ?

    Cheers
    Julian
  24. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, macdonaldj!

    Welcome to Chandoo forums!
    Checked this yet?
    http://chandoo.org/forum/forums/new-users-please-start-here.14/

    About your question, at the 2nd tab of the file uploaded by NARAYANK991 you may found the instructions and code for using this date picker userform. I said day picker userform and not date picker control or add-in, so you can't use the date picker directly from another userform but you can call it from that userform.

    Regards!
  25. macdonaldj

    macdonaldj New Member

    Messages:
    2
    Thanks SirJB7

    Yes - I managed to call it from another userform in another application and it works great.

    It's just that I have another where the calendar (when I was able to use the old Calendar Control ) was displayed along with other controls. I can do this to some extent with the MonthView but it appears to be very inflexible in terms of formatting - I can't even find a way to change its size or font - which is presumably why this userform was created. I'll just need to re-design the form so that the calendar is accessed through a button instead of actually being on the userform..

    Thanks for your help in any case..
    Last edited: Jul 28, 2017

Share This Page