• 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...

  • 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

SirJB7

Excel Rōnin
Hi, macdonaldj!

It's a pity that MS has discontinued the old DatePicker control after Office 2007. But there's a workaround, at least for 2010 version, check if it works for newer versions.

First link it's in Spanish, I didn't find it in English, and the second link is different but in English. Hope it helps.
https://support.microsoft.com/es-ar/help/2676583
https://social.msdn.microsoft.com/Forums/en-US/d147b5fa-ab97-43a9-8459-787df1e5fb38/date-picker-is-missing-again-in-excel-2010-full-installation-windows-xp?forum=isvvba

Regards!
 

schmolio007

New Member
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..
Hi macdonaldj. As others have mentioned, the date picker isn't set up to be embedded on another user form, but it can be accomplished with a bit of tweaking. The entire date picker is contained within a frame control, so you should be able to place that frame within a larger user form to achieve an embedded calendar control. You will also need to modify the function that sets and returns the selected value from the date picker, to prevent it from unloading the form.

I've created an example in the past of an embedded calendar control as a proof of concept. Hopefully it can get you pointed in the right direction. A link to the example is pasted below.

https://drive.google.com/open?id=0B60jq7LkOEFKaGlBS3RDeFc2NWM
 

Sheana

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.
Just want to say thank you so much.
 

GraH - Guido

Well-Known Member
Hi,
Read this as well. You can use date picker for Excel 32 bit (2010~2016), but not for 64 bit Excel.
https://www.ablebits.com/office-addins-blog/2016/10/12/insert-calendar-excel-datepicker-template/
I had to dig deep into my memory, but the girl and guys from The Frankens team*developed a calendar control for all office versions, including 64 bit.
https://sites.google.com/site/e90e50/calendar-control-class.
They updated it in 2017.

* Roberto Mensa, one of the three members, has a user account over here, but seems he never posted anything. What a pity...
 

shoun2502

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:
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:
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
Dear Sir,

Thank you indeed for sharing such a helpful subroutine for calendar.
Actually I was looking to enter a date on the selected cell except mentioning A SPECIFIC CELLS
Could you please help me out with that or any specific change in subroutine to execute the same.

Thanks
 
Top