Category Archives: Excel

Excel 2010’s lack of a calendar control

For some reason Microsoft have left the calendar control out of excel 2010. Which is a bit of a pain if you need to have a calendar in your sheet. Microsoft blogger sam-radakovitz has created a very good date picker control but it is an add in and so if you need to deploy it in an organisation everyone has to have it installed. This is a bit of a flaw with excel and sometimes it is easier to embed the code into your excel sheet to avoid external dependencies. He also locked the add in so it is difficult to view the source. I knocked up a very basic calendar control here in an afternoon which anyone is welcome to use if they have the need to embed a datepicker into their spreadsheets.  Its not perfect, I could clean up and comment the code a bit better and  it doesn’t do things like position the form underneath the cell but it is functional for day to day use.

There  are 8 steps to using it in your excel sheet

  • Download the zip file including the form from here into a directory on your local drive
  • In the developer tab click Visual Basic
  • In your vba project right click and choose import file
  • choose the form you just downloaded in the first step.
  • On the spreadsheet, again in the developer tab Choose insert and the image control
  • Draw on your sheet where you want the control to be
  • Go into image properties and choose the image you want
  • Double click on the image icon you just created and insert the following code…

Dim myForm As frmCalendar
Set myForm = New frmCalendar
myForm.StorageCell = ActiveSheet.Range(“A2”) ‘ this is the cell you want ‘to update…
myForm.Show

I have put an example sheet in the zip file as well.

I hope that it helps!

I get hundreds of spam comments on this post per day.  If you’re thinking of posting spam, don’t – I won’t publish it and you are wasting your time.  If it is your job to post spam – get a new one ;-)!