Class Module: Capture TextBox Enter/Exit Events Not Working Within Frame

cromag76

New Member
Joined
Jul 25, 2016
Messages
1
Hello all,

This is a bit of a rehash from http://www.mrexcel.com/forum/excel-questions/519492-cannot-setfocus-userform-control.html

Many thanks to all of the people who have added to this post: I will refer to the Workbook posted by Jaafar Tribak: http://www.box.net/shared/50tk7f8oai

I am working on an implementation of the code much as described in the previous posts, I have numerous sheets that have many Frames containing many TextBoxes.
(In some cases I have as many as 562 TextBoxes within the Userform. Code like this is a great benefit to the end user where the active textbox can be highlighted.

The current code works as long as the TextBoxes are not located within a Frame. For the example that Jaafar posted... If TextBox3 is placed inside a Frame, the module no longer fires.

Is there any way of making the code work on all TextBoxes regardless if it is inside a Frame, or even a Frame inside a Frame?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi, cromag76

> Is there any way of making the code work on all TextBoxes regardless
> if it is inside a Frame, or even a Frame inside a Frame?
If it uses the Frame control as merely line (visual grouping),
it is the best to replace Frame by Label control of the same size.
(Each control in Frame will locate it on the Label.)

--- Label's property ---
[ Caption : nothing ]
[ BackStyle : fmBackStyleTransparent ]
[ SpecialEffect : fmSpecialEffectEtched ]
[ Format menu / Order / Send to Back]

I comment in the following about the malfunction
to be out of the timing of the Enter/Exit event by control in Frame.
Tips03 : Focus changes without passing Exit event.
http://www.h3.dion.ne.jp/~sakatsu/Excel_Tips03E.htm



[ Cannot SetFocus > userform control ]
Enter/Exit events are created by checking a change of ActiveControl
in an ENDRLESS LOOP
in the thread mentioned above.

There is a method to catch Enter/Exit event in a class module
even if you do not use the ENDRLESS LOOP (I contributed even this forum in some threads).
http://www.h3.dion.ne.jp/~sakatsu/Breakthrough_P-Ctrl_Arrays_Eng_ref.htm#C2CP


I release a class module for Control-Array for VBA which incorporated this method.
It is usable immediately if you import 3 modules (clsBpca, clsBpcaCh, modBpcaConst).
http://www.h3.dion.ne.jp/~sakatsu/Breakthrough_P-Ctrl_Arrays_Eng_ref.htm

( Sample macro )
http://www.h3.dion.ne.jp/~sakatsu/Breakthrough_P-Ctrl_Arrays_Eng.htm#Chap5-1
 
Upvote 0
I correct it.

In OnExitEventCtl5.xls, the cause that OnEnter/OnExit event does not work
when a focus moved to control in Frame is not contents of Excel_Tips03E.htm.


> If Not oPrevActiveCtl Is Form.ActiveControl Then
About control in Frame1 (e.g., TBox_F1_1, TBox_F1_2, ...), the control with the focus is "Form.Frame1.ActiveControl".
It is "Form.Frame2.ActiveControl" in Frame2 (e.g., TBox_F2_1, TBox_F2_2, ...).

While there is a focus in Frame, Form.ActiveControl remains Frame1 or Frame2 all the time.
Therefore it is determined, "there is not focus movement" in this macro.

In addition, Frame1.ActiveControl/Frame2.ActiveControl remains a control
that there was a focus last in each Frame after having changed from Frame1/Frame2 outside.


Even in this case the problem dissolves if you replace it with a Label.
 
Upvote 0
I gave this a shot today ... The following workaround is suppposed to highlight the current textbox regardless of whether the textbox is placed directly on the form or inside a frame

Code:
Option Explicit
Private Xit As Boolean
Private Const HIGHLIGHT_COLOR = vbYellow

Private Sub UserForm_Activate()
[B][COLOR=#008000]
       'Any existing startup code should run here ...[/COLOR][/B]
[B][COLOR=#008000]        '*********************************************[/COLOR][/B]

   [COLOR=#ff0000][B][I] .. Existing Code Here .. Existing Code Here .. Existing Code Here .. Existing Code Here ...[/I][/B][/COLOR]

[B][COLOR=#008000]'------------------------------------------------------------[/COLOR][/B]
[B][COLOR=#008000]'The following routines must always run at the end of the activate event[/COLOR][/B]
[B][COLOR=#008000]'-------------------------------------------------------------[/COLOR][/B]
    Call StoreInitialColors
    Call UpdateActiveCtlColor
[B][COLOR=#008000]'------------------------------------------------------------[/COLOR][/B]
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Xit = True
End Sub

Private Sub StoreInitialColors()
    Dim oCtl As Control
    
    For Each oCtl In Me.Controls
        If TypeName(oCtl) = "TextBox" Then
            oCtl.Tag = CStr(oCtl.BackColor)
        End If
    Next
End Sub
Private Sub UpdateActiveCtlColor()
    Dim oCtl As Control
    Dim lCurX As Long
    
    On Error Resume Next
    Xit = False
    Do
        For Each oCtl In Me.Controls
            With oCtl
                If TypeName(oCtl) = "TextBox" Then
                    lCurX = .CurX
                    If Err.Number = 0 Then
                        If .BackColor <> HIGHLIGHT_COLOR Then
                            .BackColor = HIGHLIGHT_COLOR
                        End If
                    Else
                        Err.Clear
                        If .BackColor <> CLng(.Tag) Then
                            .BackColor = CLng(.Tag)
                        End If
                    End If
                End If
            End With
        Next
        DoEvents
    Loop Until Xit
End Sub
 
Last edited:
Upvote 0
I have modified the code slightly to work on my userform with a frame in it, it wouldnt be hard to change it to work with nested frames as well....

all i have done is change the following code to:
Code:
Set oPrevActiveCtl = Me.ActiveControl


Code:
If TypeName(Me.ActiveControl) = "Frame" Then
    Set oPrevActiveCtl = Me.ActiveControl.ActiveControl
Else
    Set oPrevActiveCtl = Me.ActiveControl
End If

using a do while loop it would take a second to change that code again to work with nested frames

Phill
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top