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.

 

[AddinBox_Tsunoda]

Hi, Jaafar.


I'm sorry for my late response.


I investigated it in various ways and asked an expert.


In conclusion, the event catch of CommandBarButton Control by
ConnectToConnectionPoint of the class module is not possible in VBA.





As for choosing ConnectToConnectionPoint daringly,
do you not wish for the event catch by WithEvents?

====[ ThisWorkbook ]============================
Public WithEvents MacroSecurity As CommandBarButton


Private Sub MacroSecurity_Click(ByVal Ctrl As Office.CommandBarButton, _
                                 CancelDefault As Boolean)
    MsgBox "Hello!"
    'CancelDefault = True
End Sub


====[ Standard module ]========================
Sub Start()
'Id=3627 : Tool > Macro > Security
    Set ThisWorkbook.MacroSecurity = Application.CommandBars.FindControl(, 3627)
End Sub


Sub Finish()
    Set ThisWorkbook.MacroSecurity = Nothing
End Sub

In addition, it seems to be defective for an event catch by WithEvents
of the CommandBarButton control in Excel2013.

KB:2922197 (This KB only as for Japanese.)
Button control of CommandBarButton does not react in Excel2013.
https://support.microsoft.com/ja-jp/kb/2922197/ja

Summary:
In Excel2013, the CommandBarButton control not to have Tag property
does not react to an event catch by WithEvents.


Workaround:
You set Tag property for the button control that you made in CommandBarButton object.


revision：1.0 26 Dec. 2013
revision：3.0 24 Jun. 2015　

 

[Jaafar Tribak]

Thank you Tsunoda for getting back .. yes, catching the commandbarbutton Click event with the WithEvents statement no longer works after introducing the Office Ribbon (office 2007) .. the same goes for other events except the OnUpdate event of the CommandBras object which still works !

I have played a little bit with the ConnectToConnectionPoint API to catch the application, workbook and worksheet events and it works well .. It would be great if the API worked also with the Ribbon button events .. This is an interesting subject and I'll keep investigating it hoping to find some useful implementations

Regards.