change the property from userform1 to whatever you want the window caption to be when the form appears to the user (I just called mine "Calendar.") A few lines down on the left is "Caption". The form appears in the right hand side, and I changed the caption (not the name of the form) by right clicking the grey area of the new userform and selecting properties. I started with the Vb window, inserted a new userform by going to project explorer, right clicking the associated "microsoft excel objects" folder, selecting "insert" and "userform". The user could select a date, and upon selection the date would transfer into the cell, and the calendar disappear. Once the cell was clicked, a calendar popped up with today's date as a default. The requirement was to allow the user to click into a cell in a specific column (used solely for dates) - column I in my case. In case anyone else needs a similar solution in future this is what I've done: Just to conclude, I've used Perry's idea of a userform, and Taz's help to find the calendar control. Would I have to create a userform, then add the calendar programmatically? I added a userform to the project then looked at the available controls, but couldn't see the calendar control as being one of the available ones. I've had a look at you userform suggestion. I'm not sure if I understand your suggestion WRT keeping the control invisible- I'm already keeping it invisible until it's required by the user. The application top etc settings only seems to set the distance between the screen and the main MS xl window - so setting this distance will only set the object position relative to the top of the xl window, even if I allowed for the "top" distance of the active cell I'd still end up with the same problem if the active cell was toward the top or bottom extremities of the screen.Īs above, setting the control top property to a fixed amount would only work as long as you didn't scroll to a completely different row.
On Error Resume Next '< error = reference already set 'set reference to Microsoft Windows Common Controls 6.0-2 (SP4) There are several ways this can be done and, depending on your personal needs or aesthetic preferences, you may prefer this other Knowlege Base entry by DRJ that also requires a userform but uses an entirely different calendar control. Requires the use of an ActiveX control contained in mscomct2.ocx, this file and an installer for it, is available here if you don't already have it installed on your machine > ) - Note that the code in the installer can be readily modified so that this calendar control can be automatically installed on any of your projects that are to be distributed.Įspecially when pre- or post-dating spreadsheet entries it is useful to be able to view a month-view calendar to see which day of the week the date falls on. User selects a cell or cells, shows the userform, selects the required date and the chosen date is then entered in the selected cell or cells. Enter Date in Selected Cell(s) Using MS Month View Calendar Control