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

			

'**********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() SetTheRef 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 DoEvents End Sub '********************************************

How to use:

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

Test the code:

  1. (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)
  2. Select Tools/Macro/Macros.../ShowForm/Run
  3. (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.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express