|
|
|
|
|
|
Excel
|
Enter Date in Selected Cell(s) Using MS Month View Calendar Control
|
|
Ease of Use
|
Easy
|
Version tested with
|
2000, 2003
|
Submitted by:
|
johnske
|
Description:
|
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 > http://www.vbaexpress.com/kb/getarticle.php?kb_id=447) - 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.
|
Discussion:
|
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 > http://www.vbaexpress.com/kb/getarticle.php?kb_id=21
|
Code:
|
instructions for use
|
Option Explicit
Private Sub SetTheRef()
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
"{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}", 2, 0
End Sub
Sub ShowTheCalendar()
SetTheRef
Userform1.Show False
End Sub
Option Explicit
Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Selection = Format(MonthView1, "dd mmm yy")
End Sub
Private Sub UserForm_Activate()
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
DoEvents
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, http://www.vbaexpress.com/kb/getarticle.php?kb_id=447 to download the installer for mscomct2.ocx)
|
Sample File:
|
Calendar.zip 17.54KB
|
Approved by mdmackillop
|
This entry has been viewed 285 times.
|
|