Can you add control events at runtime? 1

[ssVBAdev]

I know how to add controls at runtime.

Is it possible to add control events for those controls at runtime.

For example,
I'll add a series of controls from an array (called "BookmarkArray") at runtime which I can't be sure what the name will be (

objLabel.Caption = Right(BookmarkArray(x), Len(BookmarkArray(x)) - 3)

).

I would like to add (or reference), say, a "Key_Down" event which would reference some other generic code which would know what to do when a key is pressed in that specific control.

Any thoughts???


********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[krinid]

Here's two threads which have a plethora of information related to what you're trying to do.

Dynamically altering code
thread707-614467

Control arrays and their event handlers
thread707-572718

 

[ssVBAdev]

Thanks krinid!! The first Thread really had nothing to do with it but the second thread is pretty much what I'm lookinf for... But...
I can get this to work with this Class Module (clsBtnClick)...

Public WithEvents cmdB As MSForms.CommandButton

Private Sub cmdB_Click()
Dim i As Integer
sName = cmdB.Name
i = CInt(Val(Mid(sName, 5, 1)))
Call FormName.btnProc(i)
End Sub

and this form (FormName)...

Dim cmdBArray() As New clsBtnClick

Private Sub UserForm_Initialize()
For i = 1 To 5
ReDim Preserve cmdBArray(i)
Set cmdBArray(i).cmdB = Me.Controls.Add("Forms.CommandButton.1", "cmd_" & i)
    With cmdBArray(i).cmdB
        .Caption = "Button added " & i
        .Width = 80
        .Left = 10
        .Height = 18
        .Top = -10 + i * 20
    End With
Next i
End Sub

Public Sub btnProc(i As Integer)
MsgBox "Button clicked: " & i
End Sub

Works Fine!
But in my case, I specifically want to work with Text Boxes. I assumed that I could simply change some of the coding to suit my needs but I cannot get this to work (for example only!! It's not exactly what I want but it helps me to work though this...)

I use a new Class Module (clsTxtEnter) with the following code...

Option Explicit

Public WithEvents txtB As MSForms.TextBox

Private Sub txtB_Enter()
Dim sName
Dim i As Integer

    sName = txtB.Name
    i = CInt(Val(Mid(sName, 5, 1)))
    
    Call FormName2.txtProc(i)
End Sub

and the form (FormName2) with the following code...

Option Explicit

Dim txtBArray() As New clsTxtEnter

Private Sub UserForm_Initialize()
Dim i

    For i = 1 To 5
        ReDim Preserve txtBArray(i)
        Set txtBArray(i).txtB = Me.Controls.Add("Forms.textbox.1", "txt_" & i)
        With txtBArray(i).txtB
            .Text = "Text Box " & i
            .Width = 80
            .Left = 10
            .Height = 18
            .Top = -10 + i * 20
        End With
    Next i
End Sub

Public Sub txtProc(i As Integer)
    MsgBox "Button clicked: "
End Sub

In my case, the user form appears fine with the new text boxes but when I click in a text box (thereby firing the "enter" event) the msgbox does not show up.

What I would eventually like to do is have a few events fire for these text boxes such as the BeforeUpdate event.

Any thoughts...

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[krinid]

ssVBAdev,
This code will do the trick for you:

Private Sub txtB_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        MsgBox "ENTER HAS BEEN HIT"
    End If
End Sub

The problem is that there is no "Enter" event for Textboxes created in this manner, so in effect your sub Private Sub txtB_Enter() is completely ignored. I don't know why there's no Enter event - there is for statically created ones.

To see what events there are, in the VBE code editor select txtB in the top left drop down box (object selecter), and then view the items in the event box (right hand drop down box).

 

[ssVBAdev]

krinid,
Yea... Just after I posted that message I noticed that the events were different from the static form of them (when added directly to the form) and the dynamic version of them (selecting txtB in the Class Module). I especially noticed that, in the VERY limited list of events, there is no "BeforeUpdate" or "AfterUpdate" events which is, essentially, what I am looking for. One of my text boxes needs to be formatted as currency and it just does not work right using the "Change" or "Keydown" events. Another text box may be dependant on the input from another. Easily done with static text boxes but, apparently, very difficult with dynamic ones. I can filter out certain keys using the KeyDown event (ie to only allow the user to enter integers 0 to 9) but that is not all I'm looking for.

Argh... Frustrating. Just when I thought almost anything was possible with VBA. I've got VB6 at home but have no time to write code there.

I'm hoping there are other suggestions...

I do know what 'some' of the text boxes would be and 'could' add the text boxes, with appropriate events, to the form and simply move them into position and make them visable when required, but I'd rather not hard code them in that way. I want to make my code as flexible as possible.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[combo]

If you look at MSForms library in the object browser, you can find that some textbox events come from textbox and four from control (the container). It is not possible to capture container's events using WithEvents.
Maybe the tabstrip control can be in some cases a substitute for control array in VBA.

combo

 

[ssVBAdev]

I've decided to go a long away around this issue. I Don't like to but, in this case, I think it's the only way.

Combo, Nice bit of info! That's what I wanted to hear (well, not really what I 'wanted' to hear but it's what I expected but did not know how to express).

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[llowwelll]

This is probably the best thread on this topic I have come across. Great job combo and all of the other contributors!!

I have a question with a twist on this. How do I add controls to an Excel worksheet with events at run time? My program queries a database every five minutes and displays the results on a worksheet. There might be anywhere from zero to 100 records in the query. Each record occupies one row...and I want to display a command button for each row. Then, I need to be able to use the command button's click event to execute some additional code.

Any ideas? I'd really, really appreciate any help on this!!

 

[combo]

An alternative for MSForms command button - BeforeRightClick event for the sheet, here assumed that you use four columns, the first row contains headers, and there are no more rows than table contains:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column < 5 Then
    i = Target.Row
    j = Me.UsedRange.Rows.Count
    If i > 1 And i <= j Then
        MsgBox "you clicked row " & i
    Cancel = True
    End If
Else
End If
End Sub

Another option - use good old excel forms buttons, hidden in VBA excel object model since 97 verion, but still available in 'Forms' commandbar. It is possible to assign single macro to all buttons, Caller returns the name of button clicked, buttons have to be created and destroyed with changing the table size. The code has to be in standard module:

Sub CreateButtons()
Dim oShape As Excel.Button
For i = 1 To 10
With Worksheets(1)
    Set oShape = .Buttons.Add(.Cells(i, 5).Left, .Cells(i, 5).Top, .Cells(i, 5).Width, .Cells(i, 5).Height)
End With
With oShape
    .Name = "B_" & Format(i, "000")
    .Caption = "Info"
    .Font.Bold = True
    .Font.Size = 7
    .OnAction = "ButtonClick"
End With
Next i
End Sub

Sub ButtonClick()
MsgBox Application.Caller
End Sub

Finally see the four part of my faq on events, simulating control array. The userform code has to be transferred to sheet's module, you also need to destroy old and create new array after refreshing data.
If it can be useful, the code I use for automatic web queries with query tables:

' ThisWorkbook module
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set Worksheets(1).QT = Nothing
End Sub

Private Sub Workbook_Open()
Set Worksheets(1).QT = Worksheets(1).QueryTables(1)
End Sub


' Worksheets(1) module
Public WithEvents QT As QueryTable

Private Sub QT_AfterRefresh(ByVal Success As Boolean)
' re-create ActiveX buttons
End Sub

combo