|
[ Definition ] |
|
Sub Show_HolidayListMake ( )
It is the subroutine which calls the tool ( Figure ) which creates a holiday list (1st column:
Date, 2nd column: Holiday-name). This subroutine is prepared
in the add-in. The execution
button is prepared in the demonstration workbook.
Call Show_HolidayListMake
( Usage )
Step 1
When you correct a created holiday list (an addition or deletion of the date), please
click the [Read List] button and choose the upper left cell of the holiday list.
The contents of the holiday list are
reflected in a list box and a calendar.
Step 2
a) A date to register as a holiday is
specified by the click of a calendar.
b) The [ Register ] button is clicked after inputting a holiday name into TextBox.
It is added to the end
of a list box and reflected also in a calendar.
c) In the case of the date to which
the holiday name is set, the holiday name is
displayed on TextBox. The [ Register ] button is clicked after correcting
a holiday name.
e) When you delete the holiday data
registered into the list box, please double-click
the line of the holiday.
f) The registered holiday is added to
the end of a list box.
When you sort in order of the date, please click the [ ListBox Sort ] button.
(Note) In the Japanese version, since
the national holiday name of Japan is an
automatic setup,
it registers only the holiday except the national holiday.
In the English version, a user registers all holiday names (the national holiday
is included).
Step 3
When you output on a sheet the contents
registered into the list box, please click
the [ Rewrite List ] button and specify the head cell of an output place.
The data already written in the cell
range of an output place is eliminated.
|
| |
Function ktHolidayListArray ( ByVal HolidayList As Variant
) As Variant
In displaying the date of a holiday in red, it specifies
the holiday list to be the
argument of the [Create] method. This function creates the
data of the layout
for [Create] method from a holiday list (this function is
prepared in the add-in).
In order to perform display processing of a calendar efficiently,
the 2-dimensional
array of [Date; Holiday name] is changed into the 3-dimensional array of [Month;
Date; Holiday name].
Since efficiency worsens in creating this data whenever it
displays UserForm, please
store data in Variant type variable (Public variable in a
standard module etc.) in advance.
( HolidayList )
Please specify the cell range of the holiday
list of [ Date (1st column) ; Holiday name
(2nd column) ] which were prepared on the worksheet. (A holiday list can be created
by the Show_HolidayListMake subroutine.)
Or please specify the Varian type 2-dimensional
array (refer to following) which stored
holiday list data.
Dim vntArray1 ( data-count ,
2 ) As Variant
Dim vntArray2 ( 0 To data-count - 1 , 0 To 1) As Variant
Dim vntArray3 ( 1 To data-count
, 1 To 2 ) As Variant etc.
-- Example --
Public vntHolidayList As Variant
vntHolidayList = ktHolidayListArray ( Worksheets ("Sheet1").Range ("G14:H28").Value )
- - - - - - - - - - - -
Private WithEvents MonthView1 As clsPasteCal
Private UserForm_Initialize ( )
Set MonthView = CreatePasteCal
MonthView1.Create Frame1, Date, False, False, , vntHolidayList
|
[ 2K ]
|
It defines by the declaration section (modular head) of a UserForm module
as follows.
Private WithEvents object As clsPasteCal
( object )
Please specify arbitrary names.
In a [Initialize] event procedure, the instance of a class is created as follows.
(Instance creation by the New keyword cannot be performed)
Set object = CreatePasteCal ( )
( CreatePasteCal is the function currently prepared in the
add-in )
|
[ 97 ]
|
It defines by the declaration section (modular head) of a UserForm module
as follows.
WithEvents keyword does not attach.
Private object As clsPasteCal97
( object )
Please specify arbitrary names.
In a [Initialize] event procedure, the instance of a class is created as follows.
(Instance creation by the New keyword cannot be performed)
Set object = CreatePasteCal97 ( )
( CreatePasteCal97 is the function currently prepared in the add-in )
|
|
[ Method ] |
[ 2K ]
|
object .Create Frame , BaseDate , [MonToSun] , [DTPicker] , [FormatYMD] , [HolidayList]
The method which makes a calendar in Frame control.
( Frame )
The Frame object which makes a calendar is specified.
It prepares in the following size.
MonthView style [Width=108 , Height=105]
DTPicker style [Width=108 , Height=20]
In addition, in the case of DTPicker style, the margin
of [Height=105] is required for the
bottom of Frame as calendar display size.
( BaseDate )
The initial display month becomes in "year and
month" of this date.
If it is not necessary to specify the initial display month, please specify "Date function"
as today.
( MonToSun )
The day of the week of the beginning of a week is
specified.
It is Sunday, when it omits or False is specified (Sun, Mon to Sat).
It is Monday when True is specified (Mon to Sat, Sun).
( DTPicker )
The style of a calendar is specified.
It is MonthView style, when it omits or False is specified.
It is DTPicker style when True is specified.
( FormatYMD )
It specifies in a DTPicker style. In the case of a
MonthView style, it omits.
The form of the date displayed on TextBox is specified.
Please specify in the form character which can be
used with Format function.
It becomes "yyyy/m/d" when it omits.
( HolidayList )
The Variant variable which stores the holiday data created with ktHolidayListArray
function is specified. The date specified here is
displayed in red and a holiday name is
displayed on ControlTipText.
It omits, when a holiday display is unnecessary.
(Note) In Japanese version, since the national
holiday of Japan is set up automatically,
it specifies except for the national
holiday. Since it does not set up automatically
in English version, the national
holiday is also specified.
|
[ 97 ]
|
object .Create Caller , GrpId , Frame , BaseDate ,
[MonToSun] , [DTPicker] , [FormatYMD] , [HolidayList]
The method which makes a calendar in Frame control.
( Caller )
Self-UserForm is specified. Please describe "Me" keyword.
( GrpId )
When using two or more calendar control (the number of [object] is plurality),
the "character string" which distinguishes
control is specified.
If only one is used, [empty character string ""]
is sufficient.
( Frame ) to ( HolidayList )
Please refer to explanation by clsPasteCal.
|
|
object .Clear
The method which performs "release of a class object"
etc.
Please be sure to perform in the Terminate event of UserForm.
|
|
object .EmphasisRelease
The method which cancels the emphasis display (light-blue
background) of a "day" button.
When making pop-up calendar form, it uses (please refer to
the sample of a demonstration
workbook).
When UserForm was closed by Hide method and UserForm is displayed next time, it is
displayed with the emphasis display of the clicked "day" button remained. In this case,
an emphasis display is canceled by performing EmphasisRelease
method in Activate event.
|
|
object .SetRange MinDate , MaxDate
The method which sets up the date range which can be inputted
in a calendar.
Out of the range of MinDate to MaxDate, the date of a mouse position is not indicated
by emphasis (it cannot click). Moreover, to the month besides the range, movement
becomes impossible.
When the month displayed on the calendar before SetRange
method execution is outside
the range, it moves to the month of MinDate.
A selection state is canceled when the date is selected (Value
property returns Empty
and ValueIsNull property returns True).
( MinDate )
The minimum date of the range is specified.
( MaxDate )
The maximum date of the range is specified.
|
|
object .ResetRange
The method which cancels the input range set up by the SetRange
method.
|
|
object .NextYear [Offset]
object .PrebiousYear [Offset]
object .NextMonth [Offset]
object .PrebiousMonth [Offset]
The method which changes the display month of the calendar.
It is equivalent to click operation of a spin button.
A selection state is canceled when the date is selected (Value
property returns Empty
and ValueIsNull property returns True).
( Offset )
The number of years or months which Next / Previous
moves is specified.
When it omits, it is regarded as one year or one month.
|
|
object .Today
The method which changes the display month of a calendar into this month.
A selection state is canceled when the date is selected (Value property returns Empty
and ValueIsNull property returns True).
|
|
[ Property ] |
|
object .Value [ = Variant ]
The property which acquires or sets up the date of a calendar.
[ Read / Write ]
( Getting )
A selection date is returned with a "serial value."
In the MonthView style, the date currently displayed
on the lower right.
In the DTPicker style, the date currently displayed
on TextBox.
It returns an Empty value, when the date is not selected,
or in being [Enabled=False].
It can check "whether does a Value property return
the date or an Empty value" with
the value of a ValueIsNull property.
( Setting )
The display of a calendar changes to the Month of
a specification date. And the date will
be in a selection state.
It is ignored, the case of [Enabled = False] or
[the date range is set up and the date besides the
range is specified].
|
|
object .ValueIsNull [ = Boolean ]
The property which returns the selection state of the date.
[ Read / Write ]
( Getting )
When the date is selected, False is returned,
and in not selecting, True is returned.
In [ValueIsNull = True], a Value property returns
an Empty value.
( Setting )
Specification of True cancels the selection state
of the date.
In a MonthView style the date displayed on the lower
right disappears.
In a DTPicker style, the date of TextBox disappears.
Then, a Value property returns an Empty value.
In addition, it is ignored when False is specified.
|
|
object .ValueName [ = String ]
The property which acquires the holiday name (ControlTipText) set as the date.
[ Read only ]
( Getting )
The contents of the holiday name (ControlTipText)
set as the date selected are returned.
When the date is not selected, or in being [Enabled=False],
it returns the Empty string ("").
|
|
object .Enabled [ = Boolean ]
The property which sets up / acquires the operation propriety
state of a calendar.
[Read / Write ]
It becomes the following when it cannot be operated.
(a) A calendar becomes a light color display.
(b) A ValueIsNull property returns True.
(c) A Value property returns an Empty value.
( Getting )
When it can be operated, True is returned, and False is returned when it cannot be operated.
( Setting )
If True is set up, operation will become possible,
and operation will become improper if False is set
up.
|
|
object .Holiday [ = Variant ]
The property of the same function as the HolidayList argument
of a Create method.
Later, when changing a holiday setup (an addition/deletion),
it uses.
[ Write only ]
If it is set as this Holiday property, it will be immediately
reflected and re-displayed
on a calendar.
( Setting )
The Variant value ( array ) which [ktHolidayListArray function ] created is specified.
|
|
object .CalMonth [ = Date ] Ver1.20 or later
The property which returns what month is displayed now.
[ Read only ]
( Getting )
The first day of the month currently displayed returns with a serial value.
|
|
[ Event ] |
|
Private Sub object_Click ( ByVal DateVal As Date )
The event which will be generated if the date on a calendar
is clicked.
( DateVal )
The serial value of the clicked date returns.
(Note) In the case of clsPasteCal97 class, this procedure is not an event
procedure. It is
"the ordinary Sub-Procedure" Called from the following
"Event message receipt procedure".
|
|
Private Sub object_SpinChange ( ByVal MonthVal As Date ) Ver1.20 or later
The event generated when the month changes by spin button operation of year / month.
( MonthVal )
The serial value of the first day of the month after change returns.
(Note) In the case of clsPasteCal97 class, this procedure is not an event
procedure. It is
"the ordinary Sub-Procedure" Called from the following
"Event message receipt procedure".
|
|
[ Event message receipt procedure( clsPasteCal97 only ) ] |
[ 97 ]
|
In the case of clsPasteCal97 class, please prepare the following procedure into
the UserForm module using this class.
The SpinChange event was added from Ver1.20.
This procedure name is fixed (they are two underbars between clsPasteCal97 and
Event). When you use by two or more UserForms, please prepare this procedure
for
each UserForm module.
When an event occurs, it is a common procedure for receiving the message of event
generating from the [clsPasteCal97] class by UserForm. From this common
procedure,
it branches to the click event procedure (above) of each calendar with
the value of [GrpId
and EventId].
The [Ctrl] portion is arbitrary by the following procedure name.
The character string specified by GrpId, the name of the [object], etc. are good.
Public Sub clsPasteCal97__Event _
( ByVal GrpId As String, ByVal EventId As String, ByVal DateVal As Date )
Select Case GrpId
Case "xxxx" ' "String character of GrpId" specified by the Create method
Select Case EventId
Case "Click"
Call
Ctrl_Click ( DateVal )
Case "SpinChange"
Call Ctrl_SpinChange ( DateVal )
Case Else
End Select
Case "yyyy"
‥‥‥
Case Else
End Select
End Sub
( GrpId )
Even when two or more calendar control is on UserForm,
there exists only this
one common procedure. Since the value of the message to which calendars returns,
please distribute to the event procedure of each calendar
by this [GrpId].
( EventId )
In this class, "Click" or "SpinChange"
returns.
( DateVal )
The clicked date or the first day of the month which changed returns.
Please take over to the event procedure of each calendar.
|
|
[ Example ] |
[ 2K ]
[ 2K ]
[ 2K ]
|
Private WithEvents MonthView1 As clsPasteCal
Private WithEvents DTPicker1 As clsPasteCal
'-------------------------------------------------------
Private Sub UserForm_Initialize ( )
Set MonthView1 = CreatePasteCal ( )
Set DTPicker1 = CreatePasteCal ( )
MonthView1.Create Frame1, Date
DTPicker1.Create Frame2, Date, , True, "mmm d,yyyy(ddd)"
End Sub
Private Sub UserForm_Terminate ( )
MonthView1.Clear ' Release of a class object (indispensable)
DTPicker1.Clear
Set MonthView1 = Nothing
Set DTPicker1 = Nothing
End Sub
'-------------------------------------------------------
Private Sub xxx ( )
If ( DTPicker1.ValueIsNull = False ) Then
MsgBox "Selection date = " &
Format ( DTPicker1.Value, "mmm d,yyyy(ddd)" )
End Sub
Private Sub MonthView1_Click ( ByVal DateVal As Date )
MsgBox "Selection date = " & Format ( DateVal,
"mmm d,yyyy(ddd)" )
End Sub
|
[ 97 ]
[ 97 ]
[ 97 ]
[ 97 ]
|
Private MonthView1 As clsPasteCal97
Private DTPicker1 As clsPasteCal97
'-------------------------------------------------------
Private Sub UserForm_Initialize ( )
Set MonthView1 = CreatePasteCal97 ( )
Set DTPicker1 = CreatePasteCal97 ( )
MonthView1.Create Me, "MonthView1", Frame1, Date
DTPicker1.Create Me, "DTPicker1", Frame2, Date, , True, "mmm d,yyyy(ddd)"
End Sub
Private Sub UserForm_Terminate ( )
MonthView1.Clear ' Release of a class object (indispensable)
DTPicker1.Clear
Set MonthView1 = Nothing
Set DTPicker1 = Nothing
End Sub
'-------------------------------------------------------
Private Sub xxx ( )
If ( DTPicker1.ValueIsNull = False ) Then
MsgBox "Selection date = " &
Format ( DTPicker1.Value, "mmm d,yyyy(ddd)" )
End Sub
'---------------------------------------------------------
' In the case of clsPasteCal97 class, Following [MonthView1_Click]
is not
' an event procedure. It is [the ordinary Sub-Procedure] Called
from
' [Event message receipt procedure].
Private Sub MonthView1_Click ( ByVal DateVal As Date )
MsgBox "Selection date = " & Format ( DateVal,
"mmm d,yyyy(ddd)" )
End Sub
'===============================================================
'===== Event message receipt procedure (Public) ==========================
'===============================================================
' In clsPasteCal97 class, the event message of all control registered
' into this class is sent through the following procedure.
'
' "Event message receipt procedure [clsPasteCal97__Event]
< Fixed name >"
'
' It is made to branch to the event procedure of corresponding control
' by [GrpId / EventId] (they are two underbars between clsPasteCal97 and Event).
Public Sub clsPasteCal97__Event _
( ByVal GrpId As String, ByVal EventId As String, ByVal DateVal As Date )
Select Case GrpId
Case "MonthView1"
Select Case EventId
Case "Click"
Call
MonthView1_Click ( DateVal )
' Case "SpinChange"
' Call MonthView1_SpinChange
( DateVal )
Case Else
End Select
' Case "xxxx"
' Select Case EventId
' Case "Click"
' Call xxxx_Click
( DateVal )
' Case "SpinChange"
' Call xxxx_SpinChange
( DateVal )
' Case Else
' End Select
Case Else
End Select
End Sub
|
