1

I’m struggling with handling controls that are dynamically added during runtime to a pre-existing, active userform.

The userform is called “SalesInvoices” and initially contains a 10x8 grid of Textbox and ComboBox fields (controls). These have been created within the design mode, along with a few command buttons.

After the userform is launched, the user can add and delete additional rows by clicking the appropriate buttons. The userform remains active throughout, and the original 10x8 grid cannot be removed by the user.

Aesthetically, adding, deleting, moving and resizing the controls and the containing frame all work as intended.

The issue I’m facing is with assigning event handling procedures to the additional controls.

WithEvents doesn’t appear to be useful in this scenario as I require the BeforeUpdate and AfterUpdate events to validate the entries made by the user.

As such, I’ve resorted to programmatically inserting and removing the requisite event handling procedures as and when the user adds/deletes additional rows.

However, whilst my code appears to place the additional procedures accordingly, the event handlers are not triggered by the corresponding controls.

For example, here is a snippet dealing with the addition of a single control, Net_Amount11:

Private Sub AddLineButton_Click()

    Dim new_item_no As integer
    Dim new_NetAmount As Control
    Dim LineNum As Integer
    Dim DynamicUserForm As Object

    new_item_no = 11
    Set new_NetAmount = Me.Controls("Frame6").Add("Forms.TextBox.1", "Net_Amount" & new_item_no, True)

    With new_NetAmount
    .Left = 25
    .Top = 50
    .Width = 50
    .Visible = True
    End With


    Set DynamicUserForm = ActiveWorkbook.VBProject.VBComponents("SalesInvoices")

    With DynamicUserForm.CodeModule
     LineNum = .CountOfLines + 1
        .InsertLines LineNum, _
            "Private Sub Net_Amount" & new_item_no & "_AfterUpdate()" & Chr(13) & _
            "Call validate_net_amount_SI(" & new_item_no & ")" & Chr(13) & _
            "End Sub"
    End With

End Sub

This code creates the additional control in Frame6 of the active UserForm, SalesInvoices, and it inserts the event handling procedure into the code section of that userform.

But, when I update the value in the newly created Net_Amount11 TextBox, the event handler is not triggered nor is the procedure validate_net_amount_SI called. (Note that the userform is active throughout).

No errors are reported by the Editor.

I’m all out of ideas and would be grateful for some assistance. Hopefully I’ve missed something simple.

I did wonder whether this was because the inserted code needs to be compiled before it becomes operable (is this even possible on the fly without affecting the contents of the active userform?). Or perhaps there is some mismatch between the temporary nature of the new TextBox control and the permanent code relating to the userform. I couldn't find a solution either way.

Community
  • 1
  • 1
uludapata
  • 11
  • 2
  • It can be done with custom classes. See https://stackoverflow.com/questions/1083603/vba-using-withevents-on-userforms/1923457#1923457 – Chris Melville Feb 07 '18 at 01:53
  • @ChrisMelville - the events the OP wants are not available using that method. – Tim Williams Feb 07 '18 at 01:54
  • Is there a max. number of controls likely to be added at runtime? Could you create them at design time but position the "extra" ones off the form or hide them from the user until required? – Tim Williams Feb 07 '18 at 01:57
  • @Tim Williams - Hi Tim. Thank you for reading through that and for your suggestion. I've not decided on an upper limit yet, but I expect some users to require 50-100 rows at times. That would mean creating 800 or so (100 rows of 8) controls in the design view along with 400 event handling procedures. Tedious, but doable. Could Excel handle that many controls on a single useful without running into performance/memory issues? I suspect that some users will be running this on modest i3 machines. – uludapata Feb 07 '18 at 02:45
  • @ChrisMelville Hi Chris. Cheers for the suggestion. I tried this approach and found the available events prohibitive. – uludapata Feb 07 '18 at 02:51
  • Well if you're hoping to handle that many dynamic rows then I wouldn't expect the same number of pre-made controls to result in slower performance (granted, we're talking about worse-case here...) You could script the setup of the controls and event handling code. – Tim Williams Feb 07 '18 at 05:59

0 Answers0