Trigger Enter field behaviour through class for a control

Question

I raised a query which now works as per David Zemens' instructions and BrakNicku guidance.

Problem is one of the events I want to use is Enter. Within the class, I don't get the option for this event. Is there is a way to add this to the class or trigger an Enter event for the control somehow?

I tried most of the events available within the class but none of them behave the way I need them to.

A quick background: I use Enter event to set help text for the field in focus. So every time a user enters a field, I have a help textbox that gets populated with help text.

I am unable to share the workbook.

Enter Event for which control? For textbox? If yes then you can use `tb_Enter()`. And If it is an ActiveX control in the worksheet then you can use `tb_GotFocus()`. A similar reply can be found [HERE](https://stackoverflow.com/questions/51665993/how-can-i-make-a-sub-for-a-keypress-event/51666466#51666466) — Siddharth Rout, Aug 20 '18 at 12:49
Hi @SiddharthRout: Thanks for the reply. I did try `tb_Enter()` (as it's a form control) but unfortunately it doesn't trigger when I enter a textbox field in the form. I am using `tb_Change` and that works perfectly. Only reason I could think of is that `Change` is in the list of events in the class but `Enter` is not. Is there a way to maybe add the `Enter` event such that it would trigger when user enters the textbox field? I could then use the same logic for all other field types (i.e. comobobox, optionbuttons.. etc) in the form — Zac, Aug 20 '18 at 13:11
Are you sure it is a form control? `Text Field` is disabled for worksheets. I think you may have an ActiveX control on the worksheet — Siddharth Rout, Aug 20 '18 at 13:33
BTW I am slightly confused. Is your control on a Userform or in a worksheet? — Siddharth Rout, Aug 20 '18 at 13:40
`Enter` won't fire for `WithEvents` `MSForms.TextBox` and it is pretty difficult to workaround. — BrakNicku, Aug 20 '18 at 13:46
@SiddharthRout: my control is in a form so pretty sure it's a form control :) — Zac, Aug 20 '18 at 13:57
@BrakNicku: thought that might be the case. I suspect I'll have to either come up with an alternate solution for displaying help in help textbox or update the `Enter` event for each control to call the UDF that updates the help textbox — Zac, Aug 20 '18 at 13:59
Oh in that case the code is slightly different. And yes it is possible to trap the "Enter" event. Gimme 15-20 mins while I create a sample for you — Siddharth Rout, Aug 20 '18 at 14:03

score 4 · Answer 1 · answered Aug 20 '18 at 18:58

Here another solution, (doesnot work on a MAC)

Open Notepad and copy code below and paste it in a new txt-file save it als CatchEvents.cls

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "CatchEvents"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Private Type GUID
      Data1 As Long
      Data2 As Integer
      Data3 As Integer
      Data4(0 To 7) As Byte
End Type

#If VBA7 And Win64 Then
      Private Declare PtrSafe Function ConnectToConnectionPoint Lib "shlwapi" Alias "#168" (ByVal punk As stdole.IUnknown, _
              ByRef riidEvent As GUID, ByVal fConnect As Long, ByVal punkTarget As stdole.IUnknown, ByRef pdwCookie As Long, _
              Optional ByVal ppcpOut As LongPtr) As Long
#Else
     Private Declare Function ConnectToConnectionPoint Lib "shlwapi" Alias "#168" (ByVal punk As stdole.IUnknown, ByRef riidEvent As GUID, _
              ByVal fConnect As Long, ByVal punkTarget As stdole.IUnknown, ByRef pdwCookie As Long, Optional ByVal ppcpOut As Long) As Long
#End If

Private EventGuide As GUID
Private Ck As Long
Private ctl As Object
'All Other Control-Events also possible
Public Sub MyEnter()
Attribute MyEnter.VB_UserMemId = -2147384830
  Select Case TypeName(ctl)
  Case "TextBox": MsgBox "Your code for " & ctl.Name & " here!"
  Case Else: MsgBox "You entered no TextBox but another control (" & ctl.Name & ")!"
  End Select
End Sub

Public Sub ConnectAllEvents(ByVal Connect As Boolean)
      With EventGuide
          .Data1 = &H20400
          .Data4(0) = &HC0
          .Data4(7) = &H46
      End With
      ConnectToConnectionPoint Me, EventGuide, Connect, ctl, Ck, 0&
End Sub

Public Property Let Item(Ctrl As Object)
      Set ctl = Ctrl
      Call ConnectAllEvents(True)
End Property

Public Sub Clear()
      If (Ck <> 0) Then Call ConnectAllEvents(False)
      Set ctl = Nothing
End Sub

In your VBA editor you import this File

In your Userform code you add:(when you have already an Initialize-event you combine those)

Private AllControls() As New CatchEvents 'on top

Private Sub UserForm_Initialize()
ReDim AllControls(Controls.Count - 1)
    For j = 0 To Controls.Count - 1
        AllControls(j).Item = Controls(j)
    Next
End Sub

Now every Enter-event of any control will be catched, so you have to act accordingly. Every event on a Userform can be catched this way.

Hi EvR: the `cls` file; seems like it's essentially a class. Could that code not sit in a `class` module? I'm a bit reluctant to have reliance on a file outside of the workbook as this workbook is being used world wide which would mean that everytime someone wants to use the file, they will have to have the `cls` file as well — Zac, Aug 22 '18 at 12:10
Yes it is a class but you have to import it (once) This is because the hidden Attributes which you can’t copy directly in your class, but you only have to do this once in your file — EvR, Aug 22 '18 at 14:18
This answer worked for me - without hogging the processor - Thanks EvR — Tin Bum, Jun 05 '19 at 19:57
@EvR you are legend, it worked like charm, how did you get this? let me know what material I need to study to get deep down into more ideas like this — VBAbyMBA, Apr 13 '23 at 17:16
@VBAbyMBA have a look here: https://stackoverflow.com/questions/61855925/reducing-withevent-declarations-and-subs-with-vba-and-activex/61893857#61893857 including comments and here: https://rubberduckvba.wordpress.com/2020/09/30/making-mvvm-work-in-vba-part-2-event-propagation/ — EvR, Apr 14 '23 at 14:43
@EvR Is there a way to capture the `mouse movement off` a button or label, and if so, what is the attribute that needs to be used? I am currently struggling to find a way to reference the same button or label. — VBAbyMBA, Apr 18 '23 at 08:05
Yes MouseMove is -606, MouseDown is -605 and MouseUp is -607, see also above mentioned stackoverflow link — EvR, Apr 18 '23 at 08:36
@EvR I am about to implement your code in a user form in a VBA project of MSWord. All seems to work as advertised but I am facing one little problem. After running the test form the keyboard appears disconnected in the VBE (but not the document). Making a selection in the VBE version of the form unfreezes the keyboard. Can you suggest a remedy? — Variatus, Jun 08 '23 at 17:30
Strange, does this also occur when you do not 'connect' (not running the initialize? — EvR, Jun 09 '23 at 08:13

score 3 · Answer 2 · answered Aug 20 '18 at 14:24

3

Let's say your userform (Userform1) looks like this

I am going to demonstrate the Enter Event for 2 controls. TextBox and ComboBox.

Ensure that you place the CommandButton1 first on the userform. Or alternatively, set it's TabIndex to 0. This is so that the command button takes focus first when the userform loads and you can test the Entering of TextBox and ComboBox.

Paste this in a class module. My Class module name is Class1

Option Explicit

Public WithEvents Usrfrm As UserForm1
Const MyMsg As String = "Hiya there. Did you just try to sneak into the "

Private Sub Usrfrm_OnEnter(ctrl As msforms.Control)
    Select Case True
        Case TypeName(ctrl) Like "ComboBox"
            'Call Usrfrm.Combobox_List(ctrl)
            MsgBox MyMsg & "combobox?", vbCritical, "Aha!"
        Case TypeName(ctrl) Like "TextBox"
            MsgBox MyMsg & "textbox?", vbCritical, "Aha!"
    End Select
End Sub

Paste this in the userform code area

Option Explicit

Public Event OnEnter(ctrl As msforms.Control)
Private prevCtl As msforms.Control
Private mycls As Class1
Private IsfrmUnloaded As Boolean

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_Layout()
    Call spyWhatsGoingOn
End Sub

Private Sub spyWhatsGoingOn()
    Set mycls = New Class1
    Set mycls.Usrfrm = Me

    IsfrmUnloaded = False

    Set prevCtl = Me.ActiveControl

    RaiseEvent OnEnter(Me.ActiveControl)

    Do While IsfrmUnloaded = False
        If Not prevCtl Is Nothing Then
            If Not prevCtl Is Me.ActiveControl Then
                RaiseEvent OnEnter(Me.ActiveControl)
                Me.ActiveControl.SetFocus
            End If
        End If
        Set prevCtl = Me.ActiveControl
        DoEvents
    Loop
End Sub

Demo

answered Aug 20 '18 at 14:24

Siddharth Rout

147,039
17
206
250

Hi Siddharth, I tried implementing this solution but it seems to get stuck in `Do While IsfrmUnloaded = False` loop in `spyWhatsGoingOn` sub. Not sure if it would come out of that loop because we are not setting `IsfrmUnloaded` to TRUE.. or maybe I'm missing something? – Zac Aug 20 '18 at 14:58
Did you copy the code as I posted above? The baove code is tried and tested – Siddharth Rout Aug 20 '18 at 14:59
Yes. So I created a new class called `frmEvents` and pasted the code from your class into this class. I changed the `withevents` to `Public WithEvents Usrfrm As frmNewProductExtension` (frmNewProductExtension is the name of my form). I then pasted the rest of the code in my form and changed `Private mycls As Class1` to `Private mycls As frmEvents`. Does that seem right? – Zac Aug 20 '18 at 15:03
Before you do any changes to the code, Create a new excel file and follow my `EXACT` instructions. Check if the code given above is working or not. Once you see that it works then spend some time understanding what the code does and finally implement it in your code :) – Siddharth Rout Aug 20 '18 at 15:06
Will do. I'll get back to you if I have any issue but thanks for all your help mate – Zac Aug 20 '18 at 15:08
This code is inspired by [this solution](https://www.mrexcel.com/forum/excel-questions/519492-cannot-setfocus-userform-control-3.html#post2567141). Omitting `IsfrmUnloaded` flag reset in `Terminate` event makes the loop indefinite (even after closing form), so the current version can be rewritten to `Do While True`. Solution works, but causes 100% utilization of 1 core. – BrakNicku Aug 20 '18 at 16:44
@BrakNicku: I have this code in my code database which still has the [LINK](http://www.vbforums.com/showthread.php?849775-RESOLVED-help-with-vba-control-array-i-wanna-trigger-enter-and-exit-event-of-textbox-array) from where it was taken from :) apparently the vbforums link is inspired from your link.. lol – Siddharth Rout Aug 20 '18 at 16:58
Hi guys, I was off yesterday so didn't get a change to progress this. I did try to do this in a new workbook and it worked but still haven't managed to incorporate this in my workbook. @BrakNicku: I had a look at the **inspired from** solution, I will probably take hints from there to complete the process so thanks for the link – Zac Aug 22 '18 at 12:06
Siddharth and @BrakNicku: Firstly apologies for not getting back to you guys sooner but got tied up with other work. I started looking at this yesterday.. this approach works but the problem I found was: (which is entirely my bad because I didn't add this in my description here) all my controls are in `Multipage`. So although this approach works for controls on a `UserForm`, I couldn't amend it to work for controls on `Multipage`. I am adding the approach I went with here as an answer just incase it helps someone in future search – Zac Sep 26 '18 at 08:22

score 0 · Accepted Answer · answered Sep 26 '18 at 08:28

So the approach I went with was: I already had Class Module that was trapping Change event (can be seen here). As i didnt have access to Enter event in my class, I used the KeyUp and MouseDown events in this class to set help for each control. This way user can get to a field by clicking on it or tabbing to it: help is displayed for the selected control

Trigger Enter field behaviour through class for a control

3 Answers3

Linked