HardCore XL VBA -> Enter Date In Cell(s) Using MS Month View

Welcome Guest ( Log In | Register )

Resend Validation Email

Posted in the last 24 hours: (Mouse-over to stop scrolling)

Recommended Sites: - VBA Express - JMT Forums - MrExcel - Ozgrid - MS Discussion Groups - John Walkenbach - Chip Pearson - Stephen Bullens Excel Page - xlDynamic (Bob Phillips) - JKP (Jan Karel Pieterse) - The Xcel Files (Ivan F Moala) - The Excel Maniacs (Gareth) - Scriptorium (Rembo) - ExcelKB (XL-Dennis) - and don't forget to register here - it's free!...

HardCore XL VBA -> johnske's Articles and Knowledge Base (Read Only Forums) -> Excel VBA Demo Code

Enter Date In Cell(s) Using MS Month View, Use MS monthview calendar control

Track this topic | Email this topic | Print this topic

johnske

Posted: March 20, 2006 08:49 am

HardCore MVP

Group: Site Admin
Posts: 454
Member No.: 2
Joined: March 08, 2006

Version tested with:
2000, 2003

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

'**********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
MonthView1 = Date
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)

Attached File ( Number of downloads: 133 )

Calendar.zip

--------------------------------------------------------------------------------

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

johnske	Posted: March 30, 2006 03:38 am
HardCore MVP Group: Site Admin Posts: 454 Member No.: 2 Joined: March 08, 2006	Here is an image of the MS MonthView calendar (used here on a userform) Attached Image -------------------------------------------------------------------------------- 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...

1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)

0 Members:

« Next Oldest | Excel VBA Demo Code | Next Newest »