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. )
Focus class (clsBpcaFocus / clsBpcaFocusCh)[ AddinBox Home (Japanese) ] [ English Home ] [ back to Bpca ]
(Jpn. 1st Edition : 1 Jun. 2004 ) (Jpn. Last Edition : 22 Jul. 2014 ) (Eng. Translation: 4 Oct. 2016 )
--- I want to highlight the control with the focus. ---
It is a question to hear well.
This can be realized by processing "to emphasize it by Enter event, and to remove emphasis by Exit event".
Therefore it is necessary to describe processing for Enter/Exit events of all control on UserForm.
This has much waste as both quantity of coding and efficiency.
If Enter/Exit events are unified by a class module, this waste will dissolve.
However, unfortunately it cannot catch Enter/Exit events in the class module.
( Note : It can catch Enter/Exit events in a class module now by using ConnectToConnectionPoint API.
This article wrote it before finding a method using ConnectToConnectionPoint API. )
Because it cannot unify Enter/Exit events in a class module, methods using an ENDLESS LOOP are
introduced well instead. It continuously monitor a change of ActiveControl with the ENDLESS LOOP
in the class module. And it fire a custom event by RaiseEvent if it has a change.
Here, I will review this "focus monitoring" problem once again.
Q1: Why is continuously monitoring it necessary?
A1: When does a focus move to where from where?
Because I do not know it, continuously monitoring it is necessary.
Q2: But does focus movement not occur without permission?
A2: It is the following cases that it occurs.
(1) Key operation ( Tab , Enter , Access-key )
(2) Mouse Click
(3) SetFocus method
Q3: As for (1) & (2), KeyDown/Up, MouseDown/Up events occur.
As for (3), "At this time now" is self-evident on a macro side.
Then only by checking whether ActiveControl changes at time?
A3: (3) does not have any problem.
But (1) & (2) must prepare for an event for all the control.
Then I will return to the first problem.
( This has much waste as both quantity of coding and efficiency. ).
Q4: Should you unify events using a class module?
A4: There is not means to notify UserForm of detected focus movement.
Q5: Should you use RaiseEvent?
A5: It becomes the class object array to settle much control.
In that case, it cannot describe WithEvents definition to receive a notice
of RaiseEvent on the UserForm module ( specifications of WithEvents ).
The dilemma of this problem is such a thing.
However, the problem dissolves if you apply technique of clsBpca.
The class module which I made in this way is clsBpcaFocus.
Private WithEvents FocusCtrl As clsBpcaFocus
Private Sub FocusCtrl_GotFocus(ByVal Index As Integer)
FocusCtrl.Item(Index).BackColor = &HAAAAFF 'Light Red
End Sub
Private Sub FocusCtrl_LostFocus(ByVal Index As Integer)
With FocusCtrl
'It goes back up in an initial background color by [InitBColor] property.
'The initial background color is stored at the time of
'the registration of the clsBpcaFocus class.
.Item(Index).BackColor = .InitBColor(Index)
End With
End Sub
[ Structure of focus detection class ( clsBpcaFocus ) ]
About the following controls that are input controls among MsForms control,
CommandButton, TextBox, ComboBox, ListBox, CheckBox
OptionButton, ToggleButton, SpinButton, ScrollBar
it check ActiveControl by KeyDown/KeyUp/MouseDown/MouseUp events and
it fire GotFocus/LostFocus events depending on a change of ActiveControl.
( Because SpinButton and ScrollBar do not have MouseDown/Up, it substitute it in SpinDown/Up and Change ).
In addition, it supports Frame ,MutiPage, TabStrip.
( Note 1 )
A reaction seems to be late in SpinButton and ScrollBar.
In addition, it seem to rarely miss LostFocus of ScrollBar when it come and go by Mouse-Click
between SpinButton and ScrollBar.
It is having highlights for a background color of ScrollBar, and SpinButton may be highlighted together, too.
( Note 2 )
When it perform [ Cancel = True ] of the BeforeUpdate event in the case of Tab/Enter pressing in TextBox ( last
tab order ) in Frame/MutiPage, it becomes the strange state ( the following ).
The cursor stay in TextBox, nevertheless focus highlights move to the next control of Frame/MutiPage.
This is not malfunction by the clsBpcaFocus class. It is the malfunction that originally Frame/MultiPage has.
( It have attracted attention by the highlights of the focus. )
You can confirm this symptom in sample macro as follows.
(a) It change tab order of TextBox4 in Frame1 to the last
(b) It add the following macro to UserForm module.
(c) With a test form, you input "a" into TextBox4 and push down Tab/Enter.
Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If (TextBox4.Value = "a") Then
Cancel = True
End If
End Sub
(d) A cursor stay in TextBox4. Nevertheless highlights move to TextBox of MultiPage.
Actually, ActiveControl of the UserForm level points at MultiPage1 and does not point to Frame1.
[ Bpca_Focus_V15E.zip ] ( 64 kb ) ( It works in x64. )
Bpca_Focus_V15E.zip ( Distribution file )
+-- Bpca_Focus_V15E.xls ( Class Definition and Sample Macro )
+-- ExportV15E ( Export file , Please import this file in your workbook. )
+-- clsBpcaFocus.cls
+-- clsBpcaFocusCh.cls
If warning of "<file name> is not commonly downloaded and could be dangerous"
is given by the downloading of the file, ....
1 Jun. 2004 Ver 1.0 1st version (Japanese)
22 Jul. 2014 Ver 1.5 (Japanese)
4 Oct. 2016 Ver 1.5 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) clsBpcaFocus
(b) clsBpcaFocusCh
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) clsBpcaFocusCh class is a lower class of the clsBpcaFocus 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 clsBpcaFocus
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 clsBpcaFocus
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 clsBpcaFocus
[ Method ]
object .Add MsForms Control object
Method to register Control objects making Control-Array with clsBpcaFocus 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 Control object )
Specify a control object making Control-Array.
It register all control from Me.Controls collection in For Each statement (please refer to a use example).
Because it exclude the non-input control (Label etc.) in a class, please add all control without minding input/non-input control.
object .Rgst Form
Method to generate Control-Array from the controls that you added by Add method.
After having repeated Add method, you carry out Rgst method.
( Form )
It appoint own UserForm. Please really describe Me keyword.
object .Clear
Method to perform the release of the class object.
Always carry it out by Terminate event of UserForm.
object .FocusCheck
This is a method to fire LostFocus/GotFocus events when ActiveControl changes.
It is a method used in clsBpcaFocusCh, but uses even the UserForm side.
It use FocusCheck method in the following of the UserForm module.
(1) Please describe it in UserForm_Activate to get an initial focus position.
(2) Please describe FocusCheck method just after SetFocus method.
(3) When you place control except MsForms (e.g. RefEdit),
please describe FocusCheck method for Enter event of the control.
[ 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 Control-Array 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 ) [ = Objectl ] ---- Default Property ----
Property to return individual control objects constituting Control-Array 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 Control-Array.
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 a general-purpose Object type.
If the number that you appointed is out of a range (1 to object.Count), it return Nothing as an error.
object .InitBColor ( [Index] ) [ = Long ]
Property to return Initial-Background-Color of the control object.
(The value of the BackColor property stored for Add/Rgst method run time)
[ Read only ]
Please use it for the return to Initial-Background-Color by LostFocus event.
Private Sub FocusCtrl_LostFocus(ByVal Index As Integer)
With FocusCtrl
.Item(Index).BackColor = .InitBColor(Index)
End With
( 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 )
Initial-Background-Color of the control object of the number that you appointed in Index argument returns.
If the number that you appointed is out of a range (1 to object.Count), it return -1 (minus one).
[ Event ]
Private Sub object_GotFocus ( ByVal Index As Integer )
Private Sub object_LostFocus ( ByVal Index As Integer )
When a focus moved, it fire.
( Index )
A number of the control (in the order that you added by Add method consecutive numbers from 1)
that an event fired returns.
-- Highlights of the focus --
Please change a background color of the control in GotFocus,
and go back up in an Initial-Background-Color using InitBColor property in LostFocus.
[ Q & A ]
(a) The omission of the Item property becomes the error.
(Symptom)
The following description that omitted Item property becomes the error.
ex. FocusCtrl(Index).BackColor
It does not become the error if I describe Item property.
FocusCtrl.Item(Index).BackColor
--- Answer ---
(Cause)
You performed "copy & paste" of clsBpcaFocus class module between code windows of VBE.
(Answering measure)
Please delete clsBpcaTglLbl class module by "Free module".
Thereafter, please import from export file (clsBpcaFocus.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 ]
Private WithEvents FocusCtrl As clsBpcaFocus
Private blnFormClose As Boolean
Private Sub UserForm_Initialize()
Dim objCtrl As Object
Dim i As Integer
Set FocusCtrl = New clsBpcaFocus
'All the control in Frame1/MutiPage1 is registered by Me.Controls, too.
'The inapplicable control such as RefEdit/Label are removed in a class.
For Each objCtrl In Me.Controls
FocusCtrl.Add objCtrl
Next objCtrl
FocusCtrl.Rgst Me 'Registration to clsBpcaFocus
ListBox1.List() = Array("111", "222", "333", "444", "555")
ListBox2.List() = Array("AAA", "BBB", "CCC", "DDD", "EEE")
ComboBox1.List() = Array("111", "222", "333", "444", "555")
ComboBox2.List() = Array("AAA", "BBB", "CCC", "DDD", "EEE")
ComboBox3.List() = Array("111", "222", "333", "444", "555")
ComboBox4.List() = Array("AAA", "BBB", "CCC", "DDD", "EEE")
End Sub
Private Sub UserForm_Activate()
'It acquire an initial focus position by carrying out [FocusCheck]
'just after Show method.
FocusCtrl.FocusCheck
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
blnFormClose = True
End Sub
Private Sub UserForm_Terminate()
FocusCtrl.Clear
Set FocusCtrl = Nothing
End Sub
'=========================================================
' The event by clsBpcaFocus is GotFocus and LostFocus.
'=========================================================
Private Sub FocusCtrl_GotFocus(ByVal Index As Integer)
FocusCtrl.Item(Index).BackColor = &HAAAAFF 'Light Red
End Sub
Private Sub FocusCtrl_LostFocus(ByVal Index As Integer)
'It goes back up in an initial background color by [InitBColor] property.
'The initial background color is stored at the time of
'the registration of the clsBpcaFocus class.
With FocusCtrl
.Item(Index).BackColor = .InitBColor(Index)
'If a value of TextBox is a number, it edit a comma.
If (TypeName(.Item(Index)) = "TextBox") Then
If (.Item(Index).Value = "") Then
'Nothing
ElseIf IsNumeric(.Item(Index).Value) Then
.Item(Index).Value = Format(CDbl(.Item(Index).Value), "#,##0")
Else
'Not Numeric
End If
End If
End With
End Sub
'Coexistence test with the Cancel processing by the normal Exit event
Private Sub TextBox1_Exit(ByVal Cancel As MsForms.ReturnBoolean)
If (blnFormClose = True) Then
'As it is the userform end, it do not check a value.
Exit Sub
End If
If (TextBox1.Value <> "") Then
If IsNumeric(TextBox1.Value) Then
'OK
Else
Cancel = True
Beep
End If
End If
End Sub
Private Sub CommandButton1_Click()
'After carrying out SetFocus, it always carry out [FocusCheck].
TextBox3.SetFocus
FocusCtrl.FocusCheck
End Sub
Private Sub CommandButton2_Click()
'After carrying out SetFocus, it always carry out [FocusCheck].
TextBox4.SetFocus
FocusCtrl.FocusCheck
End Sub
'== About the outside control targeted for clsBpcaFocus,
'== it change a background color by Enter/Exit event of each control.
Private Sub RefEdit1_Enter()
'About the outside control targeted for clsBpcaFocus,
'it always carry out [FocusCheck] by Enter event.
FocusCtrl.FocusCheck
RefEdit1.BackColor = &HAAAAFF 'Light Red
End Sub
Private Sub RefEdit1_Exit(ByVal Cancel As MsForms.ReturnBoolean)
RefEdit1.BackColor = vbWindowBackground
End Sub
