1

I'm trying to assign event handlers on a dynamic userform in excel and let's just say that I can't find the answer, at least none I can get to work. I need Event handled on the checkbox...

Private Sub UserForm_Initialize()
Feuil1.Select
ThisWorkbook.Generate_Client_List
Dim i As Integer
i = 1
Dim topref As Integer
topref = 25

Dim imgFile1 As Object
    Set imgFile1 = Client_picking.Controls.Add("Forms.Image.1")
    With imgFile1
        .Left = 140
        .Height = 40
        .Width = 40
        .Picture = LoadPicture("C:\Users\Temp\Desktop\Project\excel.jpg")
       End With

Dim imgFile As Object
    Set imgFile = Client_picking.Controls.Add("Forms.Image.1")
    With imgFile
        .Left = 180
        .Height = 40
        .Width = 40
        .Picture = LoadPicture("C:\Users\Temp\Desktop\Project\mail.jpg")
       End With

Dim Label As Object
    Set Label = Client_picking.Controls.Add("Forms.Label.1")
    With Label
        .Caption = "Clients"
        .Left = 35
        .top = topref - 15
        .Width = 90:
        .Height = 20
        .Object.BackStyle = 0
    End With

For Each distinctClientList In Feuil1.Range("DA3:DA10").Cells

    Dim MaCheckBox As Object
    Set MaCheckBox = Client_picking.Controls.Add("Forms.CheckBox.1")
    With MaCheckBox
        .Caption = "fichier"
        .Left = 140
        .top = topref + (20 * i)
    End With

    Dim MaCheckBoxmail As Object
    Set MaCheckBoxmail = Client_picking.Controls.Add("Forms.CheckBox.1")
    With MaCheckBoxmail
        .Caption = "mail"
        .Left = 180
        .top = topref + (20 * i)
    End With

    Dim MaTextBox As Object
    Set MaTextBox = Client_picking.Controls.Add("Forms.TextBox.1")
    With MaTextBox
        .Text = CStr(distinctClientList.Value)
        .Left = 20
        .top = topref + (20 * i)
        .Width = 90:
        .Height = 20
    End With

    i = i + 1
  Next

  Dim MyButton As Object
  Set MyButton = Client_picking.Controls.Add("Forms.CommandButton.1")
  With MyButton
    .top = topref + (20 * i) + 20
    .Left = 100
    .Caption = "Ok"
  End With

  Client_picking.Height = topref + (20 * i) + 100

ThisWorkbook.Clear_Client_List
ClearToggleList
End Sub

The end result:

the form with a list of dynamically-generated controls

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Timur Özalp
  • 21
  • 4
  • you want to add also a checkbok on the `UserForm_Initialize` event ? – Shai Rado Aug 18 '16 at 14:03
  • What's a "dynamic userform"? Did you mean "a form with dynamic controls" aka created at runtime? – Mathieu Guindon Aug 18 '16 at 14:06
  • Yep, all of the userform content is generated on activation. – Timur Özalp Aug 18 '16 at 14:10
  • If `Client_picking` is your UserForm class, then your code is adding the controls on the *default instance*, and none of that is going to work if you do `Dim frm = New Client_picking` and then `frm.Show vbModal` - consider using the `Me` keyword to refer to the *current* instance, instead of hard-coding a reference to a specific, default instance which shouldn't even be used anyway. – Mathieu Guindon Aug 18 '16 at 14:14
  • Damn going to correct that right away. Still got no clue for the original question though... – Timur Özalp Aug 18 '16 at 14:18
  • 1
    Possible duplicate of [How to add events to Controls created at runtime in Excel with VBA](http://stackoverflow.com/questions/3014421/how-to-add-events-to-controls-created-at-runtime-in-excel-with-vba) – Mathieu Guindon Aug 18 '16 at 14:19
  • ^^ [this answer](http://stackoverflow.com/a/8986622/1188513) answers your original question =) – Mathieu Guindon Aug 18 '16 at 14:21
  • Once you get everything to work as intended, I'd suggest you put that code (the form and the control classes) up for peer review on [codereview.se], to get answers giving you tips to improve maintainability, readability and overall efficiency; and sometimes an answer can uncover an unexpected bug too, like that *default instance* thing. Good luck! – Mathieu Guindon Aug 18 '16 at 14:28
  • I will do so. Thx for your help. I'm currently trying to get the inner workings of this and apply it to my problem. Looks like my aptitude to reason by analogy was exagerated. – Timur Özalp Aug 18 '16 at 14:41
  • Well i'm still stuck. I'm not able to apply this method... I must be missing something. – Timur Özalp Aug 19 '16 at 09:36

0 Answers0