lundi 20 juin 2016

Dynamically add event to checkbox in userform with vba in excel

I am creating checkboxes inside a frame on runtime, however I am having trouble creating events. The checkboxes has caption such as:

  1. Manuel operation
  2. Laser cutting
  3. and so on...

The name of the checkbox is the same as caption where space is replaced with underscore(I thought this was the easiest way to do it, could also probably have just given them a random number). What i want to achieve is a textstring which then says Laser cutting, Manuel operation, .... in the order the user clicked the textboxes and not the order they are displayed in the frame.

I guess the hard part will be if the user click several checkboxes and then decide he wants to remove one check, I could then also clear all selection and prompt user to start again. (trigger the event from true to false)

So my current code just make the the text-string dependent of its checked or not and neglects the order it was checked in.

Here is the code that creates the check-boxes:

Set cTemp = MOM.Frame_MOM_MOM.Controls.Add("Forms.CheckBox.1")

                With cTemp
                    .Top = HeaderOffset + RowOffset + j * 25 'your top pos
                    .Visible = True
                    .Left = 30 + k * widthOfLabel 'your left pos
                    .Width = widthOfLabel 'your width
                    .Name = Replace(keyArrays(i, 1), " ", "_")
                    .Caption = keyArrays(i, 1) 'your caption ,
                    '.OnAction = "CheckboxChange" 'Fails badly, method does not exist it say

                End With

and here is my code that takes the information:

Public Function getMOM() As String
Dim cCont As Control

getMOM = ""
i = 1
    For Each cCont In MOM.Frame_MOM_MOM.Controls

        If TypeName(cCont) = "CheckBox" Then
            If cCont.value Then
                getMOM = getMOM + Replace(cCont.Name, "_", " ") + ","
            End If

        End If
        i = i + 1
     Next cCont



End Function

Any idea on how to achieve this?




Aucun commentaire:

Enregistrer un commentaire