in the Pseudo-Control-Array
|
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)[ AddinBox Home (Japanese) ] [ English Home ] [ back to Bpca ]
( 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.
