• 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

Status
Not open for further replies.
macdonaldj said:
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..
Click to expand...

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
 
schmolio007 said:
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.
Click to expand...

Just want to say thank you so much.
 
Hi,
Chihiro said:
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/
Click to expand...
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...
 
schmolio007 said:
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.

upsk7.png


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
Click to expand...
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
 
you are truly amazing :)
had to add application.Left to position it right when working with multiple monitors.

Truly amazing.. THANK YOU

>>> use code - tags <<<
Code: 
    If PositionTop <> -5 And PositionLeft <> -5 Then
        Me.StartUpPosition = 0
        Me.Top = PositionTop
        Me.Left = PositionLeft
    Else
        Me.StartUpPosition = 1
      
        Me.StartUpPosition = 0
        Me.Top = ActiveCell.Top + ActiveCell.Height + Me.Height
        Me.Left = Application.Left + ActiveCell.Offset(0, 1).Left
      
    End If
 
Last edited by a moderator:
Hi,
I am using this calendar control. Its being working absolutely fine in the subroutine of user form. However, at one of textboxes, i want calendar to display in month view with default value of previous month and year. I am sure there must be a way out to display the calendar form in month view and return the value to text box in "mmm-yyyy" format. I have tried a few tricks like me.textbox1.value = format(calendarform.GetDate,"mmm-yyyy") but it didn't work.
Also, the position of calendar control is required to beside to respective textbox instead appearing in the middle of the form.

Any hopes to get this resolved.
 
schmolio007 said:
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.

upsk7.png


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
Click to expand...
Excelente!!! Yo vivo en México y no soy programador, me podrías ayudar para cambiar el formato de la fecha a dd/mm/aaaa? gracias
 
Status
Not open for further replies.
Back
Top