Breakthrough
              in the Pseudo-Control-Array

[ back to Bpca ]
      ( Because I who am poor at English am translating into English while using translation software,  
        there may be an odd expression. The mistranslation revises it sequentially. )

ToggleLabel-Button class (clsBpcaTglLbl / clsBpcaTglLblCh)
    ( Flag type button only with good points of Toggle, Option and CheckBox. )
  (Jpn. 1st Edition : 15 May 2004 )
  (Jpn. Last Edition : 22 Jul. 2014 )
  (Eng. Translation: 17 Aug. 2014 )


Flag type controls to use in UserForm includes three of follows, but has both good points
and bad points in anything. Therefore, I introduce a class module implementing control
to get rid of bad points, and to take in only good points using Label control.

---  ToggleButton control  ---
    (1)  ToggleButton appears as a concave face and a convex face.
          It appears ON and OFF with a concave face and a convex face.
    (2)  With controls as the group, "exclusive choice" is not possible.
    (3)  ON/OFF turns by the click of the same control.
    (4)  Because there is a blank of top and left of the caption, it becomes a big button
          when you balance with top, bottom, right and left.

---  CheckBox control  ---
    (1)  CheckBox appears as a square box with an accompanying label.
          It appears ON and OFF with a check mark and empty.
    (2)  With controls as the group, "exclusive choice" is not possible.
    (3)  ON/OFF turns by the click of the same control.
    (4)  It can change the color of the label caption, but cannot change the color of
          a square box and a check mark.
    (5)  It is suitable for vertical placement.

---  OptionButton control  ---
    (1)  OptionButton appears as a circle box with an accompanying label.
          It appears ON and OFF with a dot mark and empty.
    (2)  With controls as the group, "exclusive choice" is possible.
    (3)  Turn-OFF by the click of the same control is not possible.
          So it is not possible to turn OFF all controls in the group with the manual.
          Operation by the macro is necessary for it.
    (4)  It can change the color of the label caption, but cannot change the color of
          a circle box and a check mark.
    (5)  It is suitable for vertical placement.



---  ToggleLabel button  by  clsBpcaTglLbl class  ---
    ToggleLabel button has the following functions by a class module.

    (1)  The button by the ToggleLabel class is made with Label control.
          It appears as a concave face and a convex face.
    (2)  Because it is become black box by a class module, you can operate by the
          manual and the macro in the same way as normal controls.
    (3)  Because it is made with Label control, there is no blank like ToggleButton
          control on the left and the top of the caption. Therefore it becomes the small
          button.
    (4)  ON/OFF turns by the click of the same control.
          So it is possible to turn OFF all controls in the group with the manual.
    (5)  With controls as the group, "exclusive choice" and "non-exclusive choice" are
          possible both.
    (6)  Setting valid or invalid as a group is possible.
    (7)  Setting of the button colors (color of the ON state and color of the OFF state)
          are possible.
    (8)  Setting of the initial state (the ON state and OFF state of each button) is possible.
    (9) If you do not check a button one by one, you can get a state of the buttons.
          a) When an ON state is one, you can get directly the button number (1,2,3...) .
          b) When there is a plural ON state, the state (-1) that is not all OFF is provided.
          c) When all buttons are the OFF state, the state is shown in Zero.


It is possible to imitate ToggleButton using the Label control by changing SpecialEffect
property by Click event of the Label control.
      fmSpecialEffectSunken(concave:ON)  fmSpecialEffectRaised(convex:OFF)

Exclusion processing can be realized by turning OFF all other buttons in the same group
when a certain button turns it ON from OFF.

However, it is hard to describe macro to operate SpecialEffect property every time.
Therefore I prepared a class module (clsBpcaTglLbl) to be easily usable.



    [ Bpca_TglLbl_V41E.zip ]   ( 87 kb )      ( It works in x64. )
            Bpca_TglLbl_V41E.zip    ( Distribution file )
                +--  Bpca_TglLbl_V41E.xls      ( Class Definition and Sample Macro )
                +--  ExportV41E    ( Export file , Please import this file in your workbook. )
                            +--  clsBpcaTglLbl.cls
                            +--  clsBpcaTglLblCh.cls

        If warning of "<file name> is not commonly downloaded and could be dangerous"
        is given by the downloading of the file, ....  

        15 May 2004    Ver 1.0    1st version (Japanese)
        22  Jul  2014        Ver 4.1        (Japanese)
        17  Aug  2014      Ver 4.1        English 1st version


(1) It is necessary to take 2 following modules in workbook by import from
     a distribution file to use this class.
        (a) clsBpcaTglLbl  
        (b) clsBpcaTglLblCh

        When you copy a class module in workbook,
                please perform it by Import by all means.
        If you perform it by "Copy & Paste" between code windows of VBE ,
                Item property does not become "Default Property"( See below )

(2) clsBpcaTglLblCh class is a lower class of the clsBpcaTglLbl class (a user cannot use
     directly this class).

(3) This class is available Excel2000 and later. (It works in x64.)



[ Link to here ]

[ Reference of Event / Method / Property ]

Within the following description, the object variable which defined clsBpcaTglLbl
class is described into the portion of "object".



[ Definition ]

    It defines by the declaration section (module head) of a UserForm module as follows.

          Private  WithEvents  object  As  clsBpcaTglLbl

    You can define plural if you change "object".

    ( object )
        Specify arbitrary names.
        In "Initialize" event procedure, the instance of a class is created as follows.

            Set  object  =  New  clsBpcaTglLbl



[ Method ]

    object .Add    MsForms.Label object

    Method to register Control objects making Toggle-Label-Button with clsBpcaTglLbl class.
    You repeat "Add" method for controls to become one group and register by "Rgst" method last.

    A control number is assigned to the order that you registered by Add method from 1.
    This number is treated in each Event or Property as Index.

    ( MsForms.Label object )
        Specify a control object making Toggle-Label-Button.


    object .Rgst    Exclusive

    Method to generate Toggle-Label-Button from the controls that you added by Add method.
    After having repeated Add method, you carry out Rgst method.

    ( Exclusive )
        It becomes exclusion by appointing of True.
        It becomes without exclusion by appointing of False.


    object .Clear

    Method to perform the release of the class object.
    Always carry it out by Terminate event of UserForm.


    object .Init    OnColor , OffColor , InitValue

    Method to set a button color in ON / OFF and the initial state of Toggle-Label-Button.
    A color in ON / OFF is changed automatically by the class when you appoint colors in OnColor / OffColor.
    The initial state of individual buttons is set by InitValue. You appoint InitValue with the initial state
    (True / False) of individual buttons as Array function.

    (OnColor / OffColor)
        You appoint button colors of ON-state (Concave) and OFF-state (Convex).
        You appoint the color in either following.
          (a) Color constants of VBA
              ex: vbRed , vbBlue
          (b) Color constants of System
              ex: vbButtonFace , vbWindowText
          (c) RGB function
              ex: RGB(0, 0, 255)
          (d) Hexadecimal constant
              ex: &HFF& (Red) , &HFF0000 (Bule)

    (InitValue)
        You appoint InitValue with the initial state (True / False) of individual buttons as Array function.
        You appoint True (ON : Concave) or False(OFF : Convex) by button order from the top in Array function.

        The start number of the button number is 1. In contrast, the start number of the array by Array
        function is zero. A start number of the button and the array slip off, but, please locate it from the
        top without minding it.

        When there is more it than the number of the buttons, the part is ignored. When there is fewer it than
        the number of the buttons, the remaining part is not initialized (remain a design in VBE).

        In the case of exclusion, it is only the first button to be turned ON even if you appoint plural True.


    object .OnValueSet   [ Index ]

    Method to change Toggle-Label-Button to ON state (True , Concave).
    You can turn ON (True , Concave) the whole group in a lump or individual buttons.
    In the case of exclusion,
        For the whole group (omit Index or appoint zero),
              Does not work (the state of buttons do not change).
        For individual buttons (appoint Index),
              A appointed button turn ON (an ON button already turn OFF at the same time).

    ( Index )
        Omissible.
        Appoint a control number (in the order that you added by Add method consecutive numbers from 1).

        In the case of non-exclusion,
              A appointed button turn ON.
              All buttons of the group turn into ON when you omit Index or appoint zero in Index.

        In the case of exclusion,
              A appointed button turn ON (an ON button already turn OFF at the same time).
              Even if you omit Index or appoint zero in Index, it does not work (the state of buttons do not change).


    object .OffValueSet   [ Index ]

    Method to change Toggle-Label-Button to OFF state (False , Convex).
    You can turn OFF (False , Convex) the whole group in a lump or individual buttons.

    ( Index )
        Omissible.
        Appoint a control number (in the order that you added by Add method consecutive numbers from 1).
        The button which you appointed becomes the OFF state (False , Convex).
        All buttons of the group turn into OFF when you omit Index or appoint zero in Index.


    You cannot use the following method in a user side.
    It is a method to send an event message from clsBpcaTglLblCh to clsBpcaTglLbl.

        RaiseClick



[ Property ]

    object .Count    [ = Integer ]

    Property to acquire the number of controls registered with Toggle-Label-Button of "object".
    [ Read only ]

    ( Getting )
        Return numerical value for the number of controls that you registered by Add method.
        This value is the maximum of "Control Number".


    object .getIndex ( CtrlName )    [ = Integer ]

    Property to acquire the value of Index (position number registered in collection) corresponding to
    the control name from a control object registered with Toggle-Label-Button of "object".
    [ Read only ]

    ( CtrlName )
        Appoint a control name by string.

    ( Getting )
        It returns a value of "Index" becoming necessary for each property from a control name.
        In the case of a non-registration name, it returns minus 1.


    object .Item ( Index )    [ = MsForms.Label ]          ---- Default Property ----

    Property to return individual Label control objects constituting Toggle-Label-Button of object.
    [ Read only ]

    (Note)
        Read-Only is a limit about the reference to the control object.
        It is not a meaning "not to be able to update each property" of the control object.


    It is property to acquire the control object when you operate individual control of Toggle-Label-Button.
    You can describe the individual propertys of the object across the period after Item property.
          example :   object .Item (1) .BackColor


    Because Item property is defined as Default-Property ,
    you can omit ".Item" and can describe as follows.      ( Q & A )

          example :   object (1) .BackColor


    ( Index )
        Appoint a control number (in the order that you added by Add method consecutive numbers
        from 1). You can appoint it by a control name if you use getIndex property together.

    ( Getting )
        The control object of the number that you appointed in Index argument returns with MsForms.Label type.
        If the number that you appointed is out of a range (1 to object.Count), it return Nothing as an error.



    object .GrpValue ( [Index] )    [ = Variant ( Integer / Boolean / #NA) ]

    Property to acquire the ON / OFF state of the button for all controls that you appointed in
    Toggle-Label-Button of "object",

    When you appoint Index, you can acquire the ON / OFF state of individual buttons.
    When you omit Index, you can acquire what number is turned on in a button group.

    [ Read  only ]

    ( Index )
        Omissible.
        Appoint a control number (in the order that you added by Add method consecutive numbers
        from 1). You can appoint it by a control name if you use getIndex property together.

    ( Getting )
        When you appoint a button number in Index,
              An ON / OFF state of the button returns in True / False.
              If the number that you appointed is out of a range (1 to object.Count), it return #NA as an error.

        When you omit Index or appoint Zero in Index,
            When all buttons are off, Zero returns.

            In the case of exclusion,
                Because the ON button is only one by the exclusion, the button number returns.

            In the case of non-exclusion,
                When an ON button is one, the button number returns .
                When plural buttons are ON, -1 (minus one) returns.


    object .GrpEnabled    [ = Boolean ]

    For all controls that you appointed in Toggle-Label-Button of "object", you enable or disable a operation.
    [ Read / Write ]

    ( Setting )
        You set a state in True ( Enable ) or False ( Disable ).

    ( Getting )
        A state returns in True or False.


[ Event ]

    Private Sub object_Click _
        ( ByVal  Index  As  Integer, ByVal Btn_Label As MSForms.Label, ByVal Btn_Value As Boolean )

    When you clicked a Toggle-Label-Button, it fires after Change event.

    (  Index  )
        A number of the control (in the order that you added by Add method consecutive numbers from 1)
        that an event fired returns.

    ( Btn_Label )
        The object of the Toggle-Label-Button (Label control) which an event fired returns.

    ( Btn_Value )
        The Button state (ON:Concave or OFF:Convex) of the result that you clicked returns in True or False.


    Private Sub object_Change _
        ( ByVal  Index  As  Integer, ByVal Btn_Label As MSForms.Label, ByVal Btn_Value As Boolean )

    When a button state (ON:Concave , OFF:Convex) of Toggle-Label-Button changed, Change event fires.
    When it change by the mouse click, Change event fires earlier than Click event.

    The Change event to fire by one operation (Mouse click or Practice of the method) is not only once.
    The Change events of all buttons that a state changed by one operation fire in succession.

    ex.  In the case of exclusion,
            When you click OFF-Button in the state that there is already ON-Button, Change event (turn OFF)
            of ON-Button and Change event (turn ON) of OFF-Button which you clicked fire in succession.
            The Click event of the Button which you clicked fires after these Chenge events.

    ex. When you omit Index by OnValueSet or OffValueSet method, the Change event of all buttons that
          a state changes fires in succession.

    (  Index  )
        A number of the control (in the order that you added by Add method consecutive numbers from 1)
        that an event fired returns.

    ( Btn_Label )
        The object of the Toggle-Label-Button (Label control) which an event fired returns.

    ( Btn_Value )
        The Button state (ON:Concave or OFF:Convex) of the result that the state changed returns in True or False.


[ Link to here ]

[ Q & A ]

      (a) The omission of the Item property becomes the error.
            (Symptom)
                  The following description that omitted Item property becomes the error.
                          ex.  WeekBtn(Index).BackColor
                  It does not become the error if I describe Item property.
                          WeekBtn.Item(Index).BackColor

      --- Answer ---
            (Cause)
                  You performed "copy & paste" of clsBpcaTglLbl class module between code windows of VBE.

            (Answering measure)
                  Please delete clsBpcaTglLbl class module by "Free module".
                  Thereafter, please import from export file (clsBpcaTglLbl.cls) in the distribution file.

            (Explanation)
                  Attribute statement (it is invisible on the code window) is written in at the procedure
                  of Item property (it is visible on the export file).
                  The Attribute statement lacks when you copy a class module by "copy & paste".
                  As a result, Default property does not work.


[ Link to here ]

[ Usage example ]

   Video Capture ( Bpca_TglLblForm1.mp4   1.0MB )


 '( Exclusion is effective )
 Private WithEvents Week1 As clsBpcaTglLbl

 Private vntWeek As Variant
 Private Const cstON As Long = vbWhite
 Private Const cstOFF As Long = &HC0FFC0    'Light Green

 Private Sub UserForm_Initialize()
 Dim i As Integer
   vntWeek = Array("", "Sunday", "Monday", "Tuesday", _
                   "Wednesday", "Thursday", "Friday", "Saturday")

   Set Week1 = New clsBpcaTglLbl
   With Week1
     .Add lblSun1
     .Add lblMon1
     .Add lblTue1
     .Add lblWed1
     .Add lblThu1
     .Add lblFri1
     .Add lblSat1
     .Rgst True      ' True is Exclusion(effective)
   End With
 End Sub

 Private Sub UserForm_Terminate()
   Week1.Clear
   Set Week1 = Nothing
 End Sub

 Private Sub cmdValue1_Click()
 Dim i As Integer
 Dim strWK As String
 Dim strWK2 As String
   'Case of GrpValue(Index=0 or omit Index)
   '  All-OFF [0]
   '  Only one ON-button [Button number of ON]
   '  Plural ON-buttons [-1]

   strWK = "(" & Week1.GrpValue & ") "
   strWK2 = " s m t w t f s"

   For i = vbSunday To vbSaturday
     If (Week1.GrpValue(i) = True) Then
       Mid(strWK2, i * 2, 1) = UCase(Mid(strWK2, i * 2, 1))
     End If
   Next i
   MsgBox strWK & strWK2
 End Sub

 Private Sub cmdTrue1_Click()
   Week1.GrpEnabled = True
 End Sub

 Private Sub cmdFalse1_Click()
   Week1.GrpEnabled = False
 End Sub

 'Private Sub cmdON1_Click()
 '  'OnValueSet(Omit Index) is became ALL-ON.
 '  Week1.OnValueSet
 '
 '  'Case of Exclusion is effective, [ALL-ON] does not work.
 'End Sub


 Private Sub cmdOFF1_Click()
   'OffValueSet(Omit Index) is became ALL-OFF.
   Week1.OffValueSet
 End Sub

 '-----( Event Procedure )-----------------------------
 Private Sub Week1_Click(ByVal Index As Integer, _
                         ByVal Btn_Label As MSForms.Label, _
                         ByVal Btn_Value As Boolean)
   lblClick1.Caption = vntWeek(Index) & ":" & Btn_Value
 End Sub

 Private Sub Week1_Change(ByVal Index As Integer, _
                         ByVal Btn_Label As MSForms.Label, _
                         ByVal Btn_Value As Boolean)
   If (Btn_Value = True) Then
     Btn_Label.BackColor = cstON
   Else
     Btn_Label.BackColor = cstOFF
   End If
 End Sub






 ' ( Exclusion is invalid )
 Private WithEvents Week2 As clsBpcaTglLbl

 Private vntWeek As Variant
 Private Const cstON As Long = vbWhite
 Private Const cstOFF As Long = &HC0FFC0    'Light Green

 Private Sub UserForm_Initialize()
 Dim i As Integer
   vntWeek = Array("", "Sunday", "Monday", "Tuesday", _
                   "Wednesday", "Thursday", "Friday", "Saturday")

   Set Week2 = New clsBpcaTglLbl
   With Week2
     .Add lblSun2
     .Add lblMon2
     .Add lblTue2
     .Add lblWed2
     .Add lblThu2
     .Add lblFri2
     .Add lblSat2
     .Rgst False    ' False is Exclusion(invalid)
   End With
 End Sub

 Private Sub UserForm_Terminate()
   Week2.Clear
   Set Week2 = Nothing
 End Sub

 Private Sub cmdValue2_Click()
 Dim i As Integer
 Dim strWK As String
 Dim strWK2 As String
   'Case of GrpValue(Index=0 or omit Index)
   '  All-OFF [0]
   '  Only one ON-button [Button number of ON]
   '  Plural ON-buttons[-1]

   strWK = "(" & Week2.GrpValue & ") "
   strWK2 = " s m t w t f s"

   For i = vbSunday To vbSaturday
     If (Week2.GrpValue(i) = True) Then
       Mid(strWK2, i * 2, 1) = UCase(Mid(strWK2, i * 2, 1))
     End If
   Next i
   MsgBox strWK & strWK2
 End Sub

 Private Sub cmdTrue2_Click()
   Week2.GrpEnabled = True
 End Sub

 Private Sub cmdFalse2_Click()
   Week2.GrpEnabled = False
 End Sub

 Private Sub cmdON2_Click()
   'OnValueSet(Omit Index) is became ALL-ON.
   Week2.OnValueSet
 End Sub

 Private Sub cmdOFF2_Click()
   'OffValueSet(Omit Index) is became ALL-OFF.
   Week2.OffValueSet
 End Sub

 '-----( Event Procedure )-----------------------------
 Private Sub Week2_Click(ByVal Index As Integer, _
                         ByVal Btn_Label As MSForms.Label, _
                         ByVal Btn_Value As Boolean)
   lblClick2.Caption = vntWeek(Index) & ":" & Btn_Value
 End Sub

 Private Sub Week2_Change(ByVal Index As Integer, _
                         ByVal Btn_Label As MSForms.Label, _
                         ByVal Btn_Value As Boolean)
   If (Btn_Value = True) Then
     Btn_Label.BackColor = cstON
   Else
     Btn_Label.BackColor = cstOFF
   End If
 End Sub





  Video Capture ( Bpca_TglLblForm2.mp4   0.7MB )


A state just after the display of the UserForm by the following macro is the figure
mentioned above. By Init method, a Button-Color and ON/OFF are automatically set.
It becomes ON/OFF state as you appointed it by Array function.

It is not necessary to perform the change of the Button-Color by macro (event procedure).


 Private WithEvents AgeBtn As clsBpcaTglLbl
 Private WithEvents SubjectBtn As clsBpcaTglLbl

 Private Const cstON As Long = vbWhite
 Private Const cstOFF As Long = &HC0FFC0    'Light Green

 Private Sub UserForm_Initialize()
 Dim i As Integer
   
   Set AgeBtn = New clsBpcaTglLbl
   With AgeBtn
     .Add lblAge10
     .Add lblAge20
     .Add lblAge30
     .Add lblAge40
     .Add lblAge50
     .Rgst True      ' True is Exclusion(effective)

     .Init vbYellow, cstOFF, Array(False, False, True, False, False)
   End With

   Set SubjectBtn = New clsBpcaTglLbl
   With SubjectBtn
     .Add lblEnglish
     .Add lblMathematics
     .Add lblScience
     .Add lblSociety
     .Add lblMusic
     .Rgst False      ' False is Exclusion(invalid)

     .Init vbCyan, vbButtonFace, Array(True, False, True, True, False)
   End With
 End Sub

 Private Sub UserForm_Terminate()
   AgeBtn.Clear
   SubjectBtn.Clear
   Set AgeBtn = Nothing
   Set SubjectBtn = Nothing
 End Sub

 'The event procedure for Button-Color changes is not necessary.






[ AddinBox Home (Japanese) ]  [ English Home ]  [ back to Bpca ]  


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