• 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

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

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:
 

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?
 

Pickles

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?
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?
 

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!
 

Taxgirl

New Member
Hi, Taxgirl!

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

About your comment, It's perfectly available, you are at 4 clicks of distance from the downloadable file.


Regards!
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!
 

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!
 

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.
Regards!
 

NARAYANK991

Excel Ninja
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!
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
 

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!
 

syd1

New Member
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
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
 

NARAYANK991

Excel Ninja
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
 

syd1

New Member
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
 

macdonaldj

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

Cheers
Julian
 

SirJB7

Excel Rōnin
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!
 

macdonaldj

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..
 
Last edited:
Top