- Code: Select all
Dim MyDate As Date
MyDate = GetDate
GetDate has three optional parameters
- Prepick (As Variant)
This is a pre-selected date which can be supplied as Date, as String or as Long integer. It is the date that will be pre-selected in the calendar when it opens. If this parameter is omitted the current date will be pre-selected. - Top and Left determine the positioning of the control on your screen
The defaults are set to 50 each which positions the calendar in the center of your application screen, even in a multiple monitor environment.
Please refer to the following link for a full description of the sub SetFormPosition.- Code: Select all
http://www.excelkey.com/forum/viewtopic.php?f=5&t=4856
Installation has been made easy.
Just copy the Function GetDate from the standard code module CalMan to any standard code module in your own VBA project. Drag the class module CalButton and the Form module DatePicker into your VBA project in the VBE's Project Explorer window - and that is all it takes.
At the top of the DatePicker code sheet you will find this enumeration. It is something like a control panel. It is the primary level of adjustment, intended and designed for the use of the uninitiated.
- Code: Select all
' all dimensions in this form are related to the DefaultFontSize(NclDFS)
' enlarge or shrink the form by modifying this enum's value
Enum Ncl ' Calendar
NclDFS = 10 ' points
NclFirstDayOfWeek = vbMonday ' specify the first day of the week
NclFirstYear = 2000 ' can't be before 1904 [1901]
NclFutureYears = 10 ' drop-down will list years from NclFirstYear
' until current year + NclFutureYears
NclMargin = 6 ' points
NclColWidth = 24 ' points
NclBackColor = 12648447 ' = Buff (&H00C0FFFF)
NclButtonColor = 12648384 ' = Light green (&HC0FFC0)
NclFontColor = 4210688 ' = Bluegreen (&H00404000)
NclFontHiColor = 16711680 ' = Blue (&H00FF0000)
NclHighlightColor = 8438015 ' = Ochre (&H0080C0FF)
End Enum
As you see, you can adjust the size by assigning another font size to enum NclDFS. I have tried 4pt to 20 pt.
If your week starts with Saturday set NclFirstDayOfWeek = VbSaturday. You can set any day as the first day of the week.
You can determine which range of years will be shown in the Year-drop-down in the form.
Modifying NclMargin or NclColumnWidth would have major and untested repercussions on the appearance of the form. I can't recommend your doing that. But you can play with the colors if you don't like what I have done.
All controls are created on the fly. Therefore all their properties are set in the form's code sheet. If you wish to make more adjustments the procedure copied below will guide you to where you might try your hand.
- Code: Select all
Private Sub UserForm_Initialize()
CreateDropDowns
CreateDayCaptions
CreateDayButtons
CreateDateDisplay
CreateCommandButtons
ResizeForm
AssignCalButtons
End Sub
For testing purposes I have invented two possible applications, to insert a date in the next blank row of the worksheet, and to insert it at cursor point, i.e. in the currently selected cell. You can try these functions in the attached workbook. The relative code is in the code sheet of Sheet1(Calendar Test) (add date to new row) and the standard code module Sample_Code (Sub TestCalendar - assigned to the Shape in the worksheet). I have commented the code for these two procedures. Neither of them is required part of the tool. Either of them can be combined with code for adding a row at the bottom of a worksheet, inheriting formats and formulas but no values, if such a row requires a date. Please refer to this link for the relative code.
- Code: Select all
http://www.excelkey.com/forum/viewtopic.php?f=5&t=2022
The calendar code was written in VB7 / Excel 2010. If you need adaptation to other versions of VBA or Excel please send me a PM or ask a question on the VBA & Macros forum, telling me which part of the code - if any - throws an error in the version you run. It should be possible to fix. Apart from the testing procedures, the code contains nothing specific to Excel and should run equally well in the environment of any other MS Office application, including MS Access, MS Word, MS PowerPoint and others.
For the moment this code is brand new and may even have some faults or shortcomings I haven't discovered yet. If and when I do improved versions will be posted here. So, if you decide to use my calendar do check here occasionally to get whatever updates there might be.
Modifications
Jan 3, 2014 - Parameter ReturnDefaultDate was removed
Jan 4, 2014 - Procedure SetFormPosition added / Code corrections (incl. removal of a major error in the test procedure)
Jan 5, 2014 - Selection drop-downs now displays 12 months / 6 years