Re: Non-activex Datepicker Calendar Control
Well, a bunch of things happened since the last post that gave me the opportunity to test the code on Windows 7 32 bit with Office 2007 and Windows 7 64 bit with Office 2010.
In addition, the users using my implementation of the date picker decided they wanted more functionality that was previously available in DTPicker from MSCOMCT2 namely the ability to increment or decrement the date using the up or down key whilst in the "date" field (which is a textbox in my implementation) ie, without actually using the drop-down calendar at all!
Getting things working for Office 2007 and Office 2010 meant contending with the delightful "ribbon" and in addition (for Office 2010) using compiler directives for VBA7. This means that Stephen Bullen's clsFormChanger module also got some rework as it uses quite a lot of APIs. Oh, and whilst there are both VBA6 and VBA7 compiler directives in the code, the VBA6 directive will also work successfully in VBA7 but not vice-versa.
Chip Pearson's form positioner code is too old to contend with Office 2007/2010 so I kludged together some changes that seem to work although I cannot guarantee this in all cases - I have had to make some assumptions on dealing with the height of the "ribbon" to get the proper vertical compensation factor. I do not have a lot of experience with the "ribbon" and I have assumed it can be moved to the left although I am pretty sure this is not so. If it can be moved, the code may not work because I could not figure out a way to move it!
Anyway, with limited testing on Excel 2007 and 2010, the pop-up calendar does seem to be placed reasonably close to the active cell in most cases, with the ribbon maximised or minimised and when the form is zoomed. This of course only applies to using the pop-up calendar directly from the worksheet, there is no impact to using the pop-up calendar on a userform as userforms don't need to use the FormPositioner module.
The additional functionality of incrementing or decrementing the date within the textbox meant that I used a "hard-coded" date format instead of vbShortDate. This was because I had to know what the format of the date field was in order to determine where the cursor was in the "date" field ie, my textbox that holds the selected date. There are APIs that can tell me the current format of vbShortDate but it started to get messy because the typical format in my case was "d/mm/yyyy" which meant I was contending with either a 1 or 2 digit day field. Instead I have coded within the Calendar module itself a CommonDateFmt variable which is initialised to "dd/mm/yyyy" for my region. This can be changed to anything of course but I recommend "dd" or "mm" rather "d" or "m" in the respective part of the field. Using this means you have to load the Calendar module first in order to initialise the value of CommonDateFmt (see the DatePicker test module). You also need to call the FormatDate function in the Calendar module to ensure all dates are in the "common format".
With this change, there is some additional code available in the Calendar module that can deal with the user pressing the up/down key in a textbox holding the date value. The textbox is enabled but locked at design time which is important. I needed it enabled to allow a key to be pressed but locked to disallow a direct change. The code determines where the cursor has been placed in the "date" field and will increment or decrement either the day, month or year portion of the date up to the min/max values you specify. You can use the mouse to move the cursor to the part of date field you want to increment or the left/right arrow keys once you are in the field.
I have now tested the code on Excel 2003 on both Windows XP and Windows 7 32 bit, Excel 2007 on Windows 7 32 bit and Excel 2010 on Windows 7 64 bit. It should work on some earlier versions of Excel possibly as far back as Excel 2000 but I certainly cannot guarantee Excel 97.