CODEWITHSUNDEEP
arraysvbaexcelcheckbox
janf
janf

Reputation: 81

Creating checkboxes dynamically from array VBA

I have a userform as following:

enter image description here

When I pick the database I want and click select, it gets me to the other page:

enter image description here

All those checkboxes are created depending on the output of an sql query.

In this example, the SQL query is:

ID            NAME
8             AI_EQ
14            BB_EQ
19            DM_EQ
22            GD_EQ

and so on...

I am adding the checkboxes in the select button as following (from a record set rst):

'creating checkboxes    
If Not rst.EOF And Not rst.BOF Then
       rst.MoveFirst
        i = 0
        Do
      With MultiPage1.Pages(1).Controls.Add("Forms.Checkbox.1", "Checkbox" & i)
.Top = yPos
.Left = 7
.Caption = rst![name]
.Width = 450
        .Height = 24
        .WordWrap = True
        .Value = False
        yPos = yPos + 17
  i = i + 1
      rst.MoveNext
End With          
            Loop Until rst.EOF
End If
 rst.Close
 cnn.Close  
Set rst = Nothing
Set cnn = Nothing

Then, I would want the Finish button considering whatever checkboxes the user selects. For example, if he selects the first two, I would want to have "8,14" ID, so that I can add them into a new SQL query.

I guess I have to create an array to achieve this instead of just having rst![name]. But my attempts of creating it have failed, and even if I had the array correctly, I wouldn't know how to adapt it to have it return the ID when selecting the NAME.

This is how I am trying to create the array (but it is not returning anything)

 Dim MyArray() As Variant
For i = 0 To rst.RecordCount
MyArray = rst.GetRows(i)
rst.MoveNext
Next

Any hints/help would be appreciated.

Upvotes: 1

Views: 1764

Answers (2)

Phantom Lord
Phantom Lord

Reputation: 701

As mentioned Siddharth Rout:

  'creating checkboxes    

If Not rst.EOF And Not rst.BOF Then
    rst.MoveFirst
    i = 0
    Do
        With MultiPage1.Pages(1).Controls.Add("Forms.Checkbox.1", "Checkbox" & i)
            .Top = yPos
            .Left = 7
            .Caption = rst![name]
            .Width = 450
            .Height = 24
            .WordWrap = True
            .Value = False
            yPos = yPos + 17
            .Tag = rst![ID]
            i = i + 1
            rst.MoveNext
        End With
    Loop Until rst.EOF
End If
rst.Close
cnn.Close  
Set rst = Nothing
Set cnn = Nothing

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149325

Since the checkboxes are created dynamically, do this.

  1. Loop through all controls and check if they are checkbox or not
  2. Check if they are checked
  3. Check if they have something in .Tag and store it in a variable.

Code

Private Sub CommandButton1_Click()
    Dim cCont As Control
    Dim cbString As String

    For Each cCont In Me.Controls
        If TypeName(cCont) = "CheckBox" Then
            If cCont.Value = True Then
                If cCont.Tag <> "" Then
                    If cbString = "" Then
                        cbString = cCont.Tag
                    Else
                        cbString = cbString & "," & cCont.Tag
                    End If
                End If
            End If
        End If
    Next cCont

    Debug.Print cbString
End Sub

But for this to work, when you are creating the checkbox, set the .Tag property to the ID value.

With MultiPage1.Pages(1).Controls.Add("Forms.Checkbox.1", "Checkbox" & i)
    '
    '~~> Rest of the code
    '
    .Tag = rst![ID]
    '
    '~~> Rest of the code
    '
End With

Upvotes: 2

Related Questions