jeudi 11 juin 2020

Dynamic CheckBox control in a MS Excel / VBA UserForm

I am trying to create a UserForm containing a number CheckBoxes for every name in a list. The tricky (for me) part is that the list of names is not constant and could vary between 0 and many names every day. So because of that I am adding the checkboxes to the UForm dynamically on runtime with the code bellow. The user should then check/uncheck the Names' boxes and depending on the user input the program continiues.

My problem is that after the interaction between the user and the UForm all the CheckBox.Values remain the same as originally initialized, regardless of the user input. I am pretty sure that the problem is due to the "dynamic" number of checkboxes because I tried the UForm with predefined checkboxes and it works fine. This doesn't help me, however...

There are many details on my UForm that I would like the user to see. I prefer check boxes to list box because I find it easier to read by users and also way easier to manage the space on the UForm. Below is what the UFrom looks like and the code that I use for reading the values. In the screenshot example I have six persons in the list, all of them added as unchecked on initialize. I have then checked three boxes out of then six. But when I click "Proceed" (Private Sub CBut_OK_Click) all the six .values remain "false" as initially assigned.

Example Screenshot

Please, any advice would be appreciated! :) Thanks!

Dim ChkBx      As MSForms.CheckBox
'using public variables from a separate module

Private Sub UserForm_Initialize()
'add checkbox for all relevant parties
    i = 0
    For i = 1 To j
        'A CheckBox for every Name
        Set ChkBx = Me.FR_List.Controls.Add("Forms.CheckBox.1", "ChkBox_" & i)
        ChkBx.Caption = People(i) & Chr(10) & "No:" & IDList(i)
        ChkBx.GroupName = "Congrats"
        ChkBx.Left = 8
        ChkBx.Top = 12 + ((i - 1) * 35)
        If Days(i) > 0 Then
            ChkBx.Value = False
        Else
            ChkBx.Value = True
        End If
        ChkBx.Width = 150
        ChkBx.AutoSize = True
    Next
End Sub

Private Sub CBut_OK_Click()
    'User has marked names to send e-mails to and pressed OK
    i = 0
    'Me.Hide
    For i = 1 To j

        'CheckBox value remains same as initial
        Debug.Print IDList(i) * 1
        Debug.Print Me.FR_List.Controls("ChkBox_" & i).Value
        Debug.Print "====="

        'This IF statement becomes pointless
        If Me.FR_List.Controls("ChkBox_" & i).Value Then
'call procedure to create and send mails
Process.EmailToPerson IDList(i) * 1
        End If
    Next
End Sub



Aucun commentaire:

Enregistrer un commentaire