AddinBox(Title_Logo)


Calendar Class Add-in
                                                                                  (Update: 2012 Feb. 19th )

( Because this class does not use Win32-API, it works in x64. )

Neither ACCESS or VB are necessary, even an API is not required.
The only thing that is needed is a Frame Control.


As for the calendar control, when the date is input, it is a very convenient
control. However, it is not built in EXCEL.

"MSCAL.ocx" used well is a control attached to ACCESS. Because it is a control
of ACCESS attachment, it is not possible to use it with the computer without
ACCESS (Office Personal etc.).
      MSCAL.ocx is extracted from the computer in which ACCESS is installed, and the act
      of installing it in other computers is a violation of the license.


On the computer in which Visual Basic is installed, 'MonthView' and 'DTPicker
that is the control of the VB attachment can be used.
      "DTPicker/MonthView" might be recorded in the control toolbox list of EXCEL by the
      computer that doesn't install Visual Basic (It is because the software made of Visual
      Basic is installed in the computer.). It is because library (mscomct2.ocx) including
      "DTPicker" and "MonthView" was included in the content of the installation of the
      software. It is possible only to use it with EXCEL as a result by chance. In this case,
      use by EXCEL is not use based on "Regular license". Moreover, it is a violation of the
      license to copy alone, and to install library (mscomct2.ocx) including "DTPicker" and
      "MonthView" only to use it by EXCEL.


DTPicker and MonthView of VB calls a common control of Windows(IE).
It is possible though DTPicker and MonthView is built into the form if a
common control is called directly by using API also by EXCEL.
(In a Japanese page of AddinBox, it introduces the method of making this
 API processing a class module. However, because the event is not used,
 this method is unsatisfied as the input tool.)

It is the most preferable to evade the restriction according to MSCAL
(ACCESS) and DTPicker / MonthView(VB), and to be able to use the
calendar control freely also with EXCEL of which computer. Therefore,
you will make the calendar input form by oneself by using UserForm.

      ( Because I who am poor at English am translating into English while using translation software,  
        there may be an odd expression. )
Japanese Link

  The calendar class [ clsPasteCal / clsPasteCal97 ] introduced here creates dynamically
the calendar control which becomes instead of MonthView / DTPicker control on UserForm.
Please include in UserForm directly, and use for it, or create and use a pop-up calendar
tool for it.
  This class is programmed using the method which I am calling the BPCA (Breakthrough in
the Pseudo-Control Arrays)
technique. ( The BPCA technique description page (Japanese) )

Illustration of clsBpca (Excel2000 or later) and clsBpca97 (Excel97 or later) concept.


[ Usage ]
    (1) A reference setup to ktClsPasteCal.xla or ktClsPasteCal97.xla is performed.
    (2) Frame (with no Caption) is prepared on UserForm by the user side.
        (When background coloring a calendar, the background color of Frame is changed.)
    (3) "clsPasteCal and clsPasteCal97" class is applied to the Frame.
    (4) The date is acquired with a Click event or a Value property.
    (5) In addition, the fonts which this software is using with UserForm and Worksheet
        are only Arial and Century.


[ Feature ]
    (a) Two styles of MonthView and DTPicker are supported.
    (b) A day of the week is supported by two arrangement, "Sun, Mon to Sat" and
        "Mon to Sat, Sun".
    (c) A public holiday, year ends and New Year holidays/anniversary of the founding,
        etc. prepare a holiday list, and registers it into a class. (In a Japanese version,
        a public holiday of Japan is automatic calculation) These dates are displayed
        in red and a holiday name is displayed on [ControlTipText]. The creation tool
        of a holiday list is prepared for the add-in. Execution is also possible from a
        demonstration book.
    (d) The date input operation is the [one click input] by the emphasis display of
        a mouse position.

[ MonthView control style ]
    Please prepare the frame of [ Width=108 , Height=105 ]. YEAR moves with a left
    side SpinButton, and MONTH moves with a right side spin button. The "day" of
    a mouse position becomes an emphasis display, and a "Click event" occurs by click.
    A selection date (it is acquirable with a Value property) is displayed on the lower
    right. (Movement of a year/a month cancels a selection date)

MonthView Style

    "Period input form" can be easily made by arranging two MonthView style calendars.
    Since the following is prepared as example form in the demonstration workbook,
    please use as it is.
Period Input Form


[ DTPicker control style ]
    Please prepare the frame of [ Width=108 , Height=20 ]. The margin of [ Height=105 ]
    is needed under a frame. (If a calendar carries out a drops down, it will change to
    [ Height=125 ].) You may arrange other control into the margin portion. When a
    calendar carries out a drops down, it is displayed that it covers and hangs over
    those control. A display and un-displaying of a calendar part switch by the click
    of a drops button. A calendar closes and the date is displayed on a text box by the
    click of the date (The format of this date can be changed by the argument).
    In addition, the font size of a text box cannot be changed.
    (Note)
        Since a DTPicker style is compact, a lot tends to be just arranged on form. However, if it arranges
        not much mostly, the response in the case of a form display may worsen. (49 control per one calendar
        is created dynamically. For example, if ten calendars are arranged on UserForm, whenever it displays
        UserFrom, 490 control will be created dynamically. By computer of a low performance, I think that a
        reaction worsens considerably.) In such a case, please make pop-up calendar form and take the method
        of calling in the double click of a text box. (How to make pop-up calendar form should refer to the sample
        currently prepared in the demonstration book)

DTPicker Style


(19-Feb-2012 addition)
(Note)  A calendar part may hide behind Frame, MultiPage, ListBox.


When Frame, MultiPage, ListBox is placed at the position lower than DTPicker-style, a calendar
part may hide behind Frame, MultiPage, ListBox. Even if ZOrder such as MultiPage is set on
"SendToBack", a calendar part may hide.

The DTPikcer-style uses the following characteristic of the Frame control (even if there is TextBox
under the DTPicker-style, a calendar part is displayed on the front of the control by this characteristic).
        "The Frame control becomes the front than other control regardless of ZOrder."

This characteristic will come from that Frame control is control to have a window-handle unlike
other control. Because MultiPage and ListBox have a window-handle, it is displayed than other
control by the front. Therefore a calendar part is not displayed as expected (displayed than other
control by the front) when MultiPage, ListBox (naturally Frame is included) is placed at the position
where a calendar part is displayed. In addition, by the number of MultiPage, ListBox, Frame control,
a calendar part hides and does not hide.

I cannot evade this problem. Therefore, please devise a layout of UserForm not to place Frame,
MultiPage, ListBox in the range where a calendar part of the DTPicker-style hangs over. When it is
never avoided in a design of UserForm, please use "popup calendar form using MonthView-style"
in substitution for DTPicker-style.




Download : Calendar class add-in & Demonstration workbook  (ReadMe)
    The "Palette class" which creates color palette form is attached.
    ( 2005 Jun. 3rd  Ver1.11 )  1st. English version
    ( 2005 Jun. 14th  Ver1.20 )  Bug correction of ValueIsNull property.
                                                The addition of SpinChange event and CalMonth property.



(Note) About Re-distribution of ktClsPasteCal.xla / ktClsPasteCal97.xla
      If you include [ktClsPasteCal.xla/ktClsPasteCal97.xla] in your distribution file (workbook which
      you created using this calendar class add-in), please observe the following license agreement.
      (1) Common
          (a) Don't change the contents of [ktClsPasteCal.xla/ktClsPasteCal97.xla].
          (b) The following description is carried to "description of ReadMe etc." attached to the software
              which the user created.
                  " A calendar class add-in [ktClsPasteCal.xla and ktClsPasteCal97.xla] is K.Tsunoda's work. "
                  [ AddinBox ]  http://addinbox.sakura.ne.jp/
                                        http://addinbox.sakura.ne.jp/Bpca_PasteCal.htm

                  --- Old Site ---[  http://www.h3.dion.ne.jp/~sakatsu/ ]
      (2) In distribution in the limited ranges, such as use in the company, you may distribute freely.
      (3) In the distribution as the freeware / share software which an individual creates, you may
          distribute freely.
      (4) About use by the selling software which a company creates
          (a) It does not grant a permission that a company uses this calendar class add-in for the selling
              software for many and unspecified users (share software is included). I think that a company
              should carry out with the technology of its company.
          (b) It permits that a company uses this calendar class add-in for the software supplied to a specific
              user. However, please give the following explanation to a delivery place user, and carry description
              of (1-b) in a delivery document.
                    " This add-in is distributed as freeware. It is not what was developed at its company. "



====================================================================
   Event / Method / Property of [ clsPasteCal and clsPasteCal97 ] class 
====================================================================
          [clsPasteCal] class can be used in Excel2000 or later.
          [clsPasteCal97] class is used in Excel97. ( [clsPasteCal97] class is used also when using by both
          Excel2000 or later and Excel97. )


          Within the following description, the object variable which defined clsPasteCal / clsPasteCal97
          class is described into the portion of [ object ].

          [2K] notation is the specification of clsPasteCal. [97] notation is the specification of clsPasteCal97.
          The things without the notation are common specifications.

          To use this class, it is necessary to refer to ktClsPasteCal.xla or ktClsPasteCal97.xla.

          Because this class does not use Win32-API, it works in x64.


[ 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


 





====================================================================
   Event / Method / Property of [ clsPalette and clsPalette97 ] class 
====================================================================
If the same technique as a clsPasteCal class is used, the class which creates color
palette form can also be made. Since the example used as pop-up palette form is
prepared for the demonstration workbook, please refer to it.
Color Palette form

          [clsPalette] class can be used in Excel2000 or later.
          [clsPalette97] class is used in Excel97. ( [clsPalette97] class is used also when using by both
          Excel2000 or later and Excel97. )


          Within the following description, the object variable which defined clsPalette / clsPalette97
          class is described into the portion of [ object ].

          [2K] notation is the specification of clsPalette. [97] notation is the specification of clsPalette97.
          The things without the notation are common specifications.

          To use this class, it is necessary to refer to ktClsPasteCal.xla or ktClsPasteCal97.xla.


[ Definition ]
[ 2K ]
It defines by the declaration section (modular head) of a UserForm module as follows.

    Private  WithEvents  object  As  clsPalette

    ( 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  = CreatePalette ( )

    ( CreatePalette 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  clsPalette97

    ( 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  = CreatePalette97 ( )

    ( CreatePalette97 is the function currently prepared in the add-in )


[ Method ]
[ 2K ]
object .Create    Frame , Book

    The method which makes a color palette in Frame control.
    Arrangement of ColorIndex on the created color palette is the same as Excel.

    ( Frame )
      The Frame object which makes a color palette is specified.
      It prepares in the following size.
      [Width=105 , Height=97]

    ( Book )
      A Workbook object is specified. The color scheme set as the workbook specified here is
      applied. Usually, please specify [ThisWorkbook]. When you specify a specific workbook,
      please specify by the Workbooks collection or [ActiveWorkbook].
 

[ 97 ]
object .Create    Caller , GrpId , Frame , Book

    The method which makes a color palette in Frame control.
    Arrangement of ColorIndex on the created color palette is the same as Excel.

    ( Caller )
      Self-UserForm is specified. Please describe "Me" keyword.

    ( GrpId )
      When using two or more color palette (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 ) 〜 ( Book )
      Please refer to explanation by clsPalette.
 

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 (Convex) of a "color" button.

    When making pop-up color palette 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 "color" button remained.
    In this case, an emphasis display is canceled by performing EmphasisRelease method
    in Activate event.


[ Event ]
Private Sub object_Click ( ByVal ColorIdx As Integer, ByVal ColorVal As Long )

    The event which will be generated if the color on a color palette is clicked.

    ( ColorIdx )
      The ColorIndex value (1-56) of the clicked color returns.
    ( ColorVal )
      The Color value (&H000000 - &HFFFFFF) of the clicked color returns.

(Note) In the case of clsPalette97 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( clsPalette97 only )
[ 97 ]
In the case of clsPalette97 class, please prepare the following procedure into
the UserForm module using this class.


This procedure name is fixed (they are two underbars between clsPalette97 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 [clsPalette97] class by UserForm. From this common procedure,
it branches to the click event procedure (above) of each color palette with the value of [GrpId].

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 clsPalette97__Event _
    ( ByVal GrpId As String, ByVal EventId As String, _
      ByVal ColorIdx As Integer, ByVal ColorVal As Long )

    Select Case GrpId
        Case "xxxx"    ' "String character of GrpId" specified by the Create method
                Call Ctrl_Click ( ColorIdx, ColorVal )
        Case "yyyy"
                ‥‥‥
         Case Else
    End Select
End Sub


    ( GrpId )
      Even when two or more color palette is on UserForm, there exists only this
      one common procedure. Since the value of the message to which color palette
      returns, please distribute to the event procedure of each color palette by this
      [GrpId].

    ( EventId )
      In this class, "Click" returns fixed.

    ( ColorIdx )
      The ColorIndex value of the clicked color returns.
      Please take over to the event procedure of each color palette.

    ( ColorVal )
      The Color value of the clicked color returns.
      Please take over to the event procedure of each color palette.

 

[ Example ]

Please refer to the sample of a demonstration workbook.

 

[ AddinBox Top page (Japanese) ] [ English Top page ]



AddinBox ( K.Tsunoda in Japan ) CopyRight(C) 2005 Allrights Reserved.