TextBox created at run-time won't fire some events.

[Darren Bartrup]

Hi all,

I'm having trouble capturing events on controls created at run-time.

I create a form and add two text boxes.

The code behind TextBox1 is:

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "Before Update"
End Sub

Now, when I update the info and go to exit the control the message box is displayed.

--------------------

Next, I add a frame to the form called Frame1 and a class module called Class1.
I add this code to Class1:

Public WithEvents txtBx As MSForms.TextBox
Private fme As Frame


'Doesn't work.
Private Sub txtBx_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "Before Update"
End Sub


'Works.
Private Sub txtBx_Change()
    MsgBox "Change"
End Sub


'Works.
Private Sub txtBx_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "Double Click"
End Sub


'Doesn't work.
Private Sub txtBx_Enter()
    MsgBox "Enter."
End Sub


Public Property Set ContainerFrame(FrameReference As Frame)
    Set fme = FrameReference
End Property


Public Sub AddNewTextBox()
    Dim ctrlTextBox As Control
    Set ctrlTextBox = fme.Controls.Add("Forms.TextBox.1", "MyTextBox1")
    With ctrlTextBox
        .Left = 10
        .Height = 24
        .Width = 100
        .Top = 1 * (24 + 5) + 12
        .Text = "Starting Value"
    End With
    Set txtBx = ctrlTextBox
End Sub

Finally, I add this code to the form:

Public TextBoxEvent As Collection

Private Sub UserForm_Initialize()


    Dim NewTextBox As Class1
    Dim NewControl As Control
    
    Set TextBoxEvent = New Collection
    Set NewTextBox = New Class1
    
    With NewTextBox
    
        Set .ContainerFrame = Me.Frame1
        .AddNewTextBox
        
    End With
    TextBoxEvent.Add NewTextBox
    
End Sub

The problem is that the Double-Click event is captured and fires as expected, the Change event is captured and fires but the BeforeUpdate event and Enter events are apparently ignored.

Does anyone have any idea why these events are ignored?
Any help is greatly appreciated.

 

[Jaafar Tribak]

the BeforeUpdate and Enter events are container events and therefore are not exposed by the TextBox Control
You could try emulating those events by watching when the textbox gets/loses the focus .. you could for example use a loop for this
How many textboxes/controls will you have in the Frame control ?

 

[Darren Bartrup]

Thanks for the reply Jaafar.

Ok, I understand now why it's not working - although I'm not exactly sure where the line between the control and container is.
I have read on another forum:

To capture the AfterUpdate event, you need to create a Class for the parent object (since we're not allowed inheritence in VBA), which is the 'MSForms.Control'

This would be to create a class for the frame control or the form and link to it somehow through that?

It's definitely something I want to figure out for future reference, but I think I'll lock the text box and have it update via a button for now.

There could be any number of controls as it reads from an Access table and allows the user to add and remove extra data.
When working I wouldn't envision it having more than 10 rows - each row containing a command button and two text boxes.

 

[RoryA]

Whoever wrote that had clearly never tried it. You can't declare an MSForms.Control variable WithEvents.

 

[Darren Bartrup]

Thanks for the heads up Rory. Will save me wasting some time trying to figure it out.

 

[AddinBox_Tsunoda]

Hi, Darren.


Frame is to try a matter of "I have read on another forum:", and is Frame not really necessary?
Is it to catch an event of Enter/BeforeUpdate at run-time that is necessary?


There is the method to perform a catch of
Enter/Exit/BeforeUpdate/AfterUpdate in a class module (or catch it at run-time) .


I introduced the method in this forum several times,
but was not able to attract the interest of everybody.
(Probably it will be my fault that English is poor. I depend on translation software).


You can catch
Enter/Exit/BeforeUpdate/AfterUpdate in a class module
by using API:ConnectToConnectionPoint.

I explain the method in the following site.


Implementation of the event handling by API : ConnectToConnectionPoint
http://www.h3.dion.ne.jp/~sakatsu/Breakthrough_P-Ctrl_Arrays_Eng_ref.htm#C2CP






Breakthrough in the Pseudo-Control-Array
http://www.h3.dion.ne.jp/~sakatsu/Breakthrough_P-Ctrl_Arrays_Eng.htm


General-purpose class module ( clsBpca )
http://www.h3.dion.ne.jp/~sakatsu/Breakthrough_P-Ctrl_Arrays_Eng_ref.htm




The event catch by ConnectToConnectionPoint is implemented to clsBpca class module.
Sample macro by clsBpca
http://www.h3.dion.ne.jp/~sakatsu/Breakthrough_P-Ctrl_Arrays_Eng.htm#Chap5-1

 

[Darren Bartrup]

Hi Tsunoda,

I will definitely give those sites a thorough read and try to implement the code.
For the moment I've had to move on to another form (populating list box with email details and moving emails from one folder to another) so I've got something to show the managers.

 

[Jaafar Tribak]

AddinBox_Tsunoda ,

Nice code !! Thank you very much for teaching us something new

 

[Benzadeus]

I remember when I studied Tsunoda's site content. It gave me headaches, he is the best when it comes to UserForm events.

 

[Jaafar Tribak]

Hi Tsunoda,

Have you had any luck making this thechnic work with Office Commandbar button click event ?

The code below doesn't work ... the ConnectToConnectionPoint function fails and returns the HRESULT error code : &H80040200 instead of S_OK

Class1 Code:

Option Explicit
 
Private Declare _
Function IIDFromString Lib "ole32.dll" ( _
    ByVal lpsz As Long, _
    lpiid As GUID _
) As Long

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
 
Private cookie As Long
Private iid As GUID
Private oButton As Object
 
Private Sub Class_Initialize()
    Dim iid As GUID
    Dim lRet As Long
    
   [B][COLOR=#008000] '_ComandBraButtonEvents IID[/COLOR][/B]
    Const sIID = "{000C0351-0000-0000-C000-000000000046}"
    lRet = IIDFromString(StrPtr(sIID), iid)
    Debug.Print lRet
    [B][COLOR=#008000]'Hook the Macros Security commandbar button on the office ribbon[/COLOR][/B]
    Set oButton = Application.CommandBars.FindControl(, 3627)
    lRet = ConnectToConnectionPoint(Me, iid, 1, oButton, cookie)
    Debug.Print Hex(lRet)
End Sub

 
Private Sub Class_Terminate()
    Dim lRet As Long
    lRet = ConnectToConnectionPoint(Nothing, iid, 0, oButton, cookie)
    Debug.Print Hex(lRet), cookie
    If lRet <> 0 Then Exit Sub
End Sub

[B][COLOR=#008000]'Click DispID =1[/COLOR][/B]
Public Sub HookClick(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    [B][COLOR=#ff0000]Attribute HookClick.VB_UserMemId = 1[/COLOR][/B]
    MsgBox "hello!"
End Sub

Usage in a Standard module :

Option Explicit
 
Private cls As Class1
 
Sub Start()
    Set cls = New Class1
End Sub
 
Sub Finish()
    Set cls = Nothing
End Sub

Any idea ?