Remove this ad


HardCore MVP

Posts: 493


Mar 20 06 8:49 AM

Tags : :

Version tested with:
2000, 2003

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

(N.B. 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.

Especially 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.

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. It can be found here >

'**********CODE FOR STANDARD MODULE**********

Option Explicit

Private Sub SetTheRef()
   On Error Resume Next '< error = reference already set
    'set reference to Microsoft Windows Common Controls 6.0-2 (SP4)
   ThisWorkbook.VBProject.References.AddFromGuid _
   "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}", 2, 0
End Sub

Sub ShowTheCalendar()
   Userform1.Show False '< delete/comment out False for Office '97
End Sub

'*********CODE FOR USERFORM MODULE**********

Option Explicit

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
    'format date another way if you want to change
   Selection = Format(MonthView1, "dd mmm yy")
End Sub

Private Sub UserForm_Activate()
    'this sets all the sizes + calendar position
   With Userform1
       .Caption = "Select a Cell, Then a Date"
       .Height = 145
       .Width = 142
   End With
   With MonthView1
       .Height = 120
       .Width = 130
       .Left = 4
       .Top = 4
   End With
   MonthView1 = Date
End Sub

How to use:
Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
Select Insert/Module, copy and paste the code (above) for the standard module in this modules code pane
Put the mouse pointer anywhere inside the code for 'Private Sub SetTheRef()'
Now click Run/Run Sub/UserForm in the VBE toolbar (this sets a required reference)
Select Insert/Userform (this will create Userform1)
Select Tools/Additional Controls & check 'Microsoft MonthView Control 6.0 (SP4)'
On the toolbox, select 'Month View' and (similarly) move the pointer down and to the right to create a calendar
Double-click the userform and a code pane will appear
Delete any code that's in this code pane and copy and paste the code for Userform1 from above
Now select File/Close and Return To Microsoft Excel
Save your work

Test the code:
(N.B. If using Office '97 you first need to delete or comment out 'False' in the standard module, you will then also need to make your selection before showing the calendar)
Select Tools/Macro/Macros.../ShowForm/Run

(NOTE: If you receive any error messages, you will need to follow this link, to download the installer for mscomct2.ocx)
Click here to view the attachment

If I irradiate my cat will that give it eighteen half-lives?

The major part of getting the right answer lies in asking the right question...

Quote    Reply   
Remove this ad
Remove this ad
Add Reply

Quick Reply

bbcode help