Populate Userform with Checkboxes for each value in column A

[Bluewhistler]

Hello again,

How can I make a userform populate with checkboxes for every cell in column A on sheet1 wich containing a text and the caption being the cellvalue?

I'm assuming it must be done between in UserForm_Initialize()


Private Sub UserForm_Initialize()

checkboxes.add range(sheet2!A1:A200) 'no chxbxes for empty cells!'

'Add checkbox starting from top left and going downwards and starting a new row when the botom of the userform is reached.'
End Sub

any help would be apriciated
thx.

[pike]

a good place to star would be to peruse this code

Option Explicit

Sub AddUserForm()
    Dim objVBProj As VBProject
    Dim objVBComp As VBComponent
    Dim objVBFrm As UserForm
    Dim objChkBox As Object
    Dim x As Integer
    Dim strCode As String
     
    Set objVBProj = Application.VBE.ActiveVBProject
    Set objVBComp = objVBProj.VBComponents.Add(vbext_ct_MSForm)
    
    With objVBComp
    ' read form's name and other properties
        Debug.Print "Default Name " & .Name
        Debug.Print "Caption: " & .DesignerWindow.Caption
        Debug.Print "Form is open in the Designer window: " & .HasOpenDesigner
        Debug.Print "Form Name " & .Name
        Debug.Print "Default Width " & .Properties("Width")
        Debug.Print "Default Height " & .Properties("Height")

    ' ret form's name, caption and size
        .Name = "ReportSelector"
        .Properties("Width") = 250
        .Properties("Height") = 250
        .Properties("Caption") = "Request Report"
    End With
      
      Set objVBFrm = objVBComp.Designer
      With objVBFrm
           With .Controls.Add("Forms.Label.1", "lbName")
                .Caption = "Department:"
                .AutoSize = True
                .Width = 48
                .Top = 30
                .Left = 20
            End With
        
            With .Controls.Add("Forms.Combobox.1", "cboDept")
                .Width = 110
                .Top = 30
                .Left = 70
            End With
    
            ' add frame control
            With .Controls.Add("Forms.Frame.1", "frReports")
                .Caption = "Choose Report Type"
                .Top = 60
                .Left = 18
                .Height = 96
            End With
            
            ' add three check boxes
            Set objChkBox = .frReports.Controls.Add("Forms.CheckBox.1")
            With objChkBox
                 .Name = "chk1"
                 .Caption = "Last Month's Performance Report"
                 .WordWrap = False
                 .Left = 12
                 .Top = 12
                 .Height = 20
                 .Width = 186
            End With
            
            Set objChkBox = .frReports.Controls.Add("Forms.CheckBox.1")
            With objChkBox
                 .Name = "chk2"
                 .Caption = "Last Qtr. Performance Report"
                 .WordWrap = False
                 .Left = 12
                 .Top = 32
                 .Height = 20
                 .Width = 186
            End With
            
            Set objChkBox = .frReports.Controls.Add("Forms.CheckBox.1")
            With objChkBox
                 .Name = "chk3"
                 .Caption = Year(Now) - 1 & " Performance Report"
                 .WordWrap = False
                 .Left = 12
                 .Top = 54
                 .Height = 20
                 .Width = 186
            End With

            ' Add and position OK and Cancel buttons
              With .Controls.Add("Forms.CommandButton.1", "cmdOK")
                  .Caption = "OK"
                  .Default = "True"
                  .Height = 20
                  .Width = 60
                  .Top = objVBFrm.InsideHeight - .Height - 20
                  .Left = objVBFrm.InsideWidth - .Width - 10
              End With
            
                With .Controls.Add("Forms.CommandButton.1", "cmdCancel")
                    .Caption = "Cancel"
                    .Height = 20
                    .Width = 60
                    .Top = objVBFrm.InsideHeight - .Height - 20
                    .Left = objVBFrm.InsideWidth - .Width - 80
                End With
    End With
    
     'populate the combo box
     With objVBComp.CodeModule
        x = .CountOfLines
        .InsertLines x + 1, "Sub UserForm_Initialize()"
        .InsertLines x + 2, vbTab & "With Me.cboDept"
        .InsertLines x + 3, vbTab & vbTab & ".addItem ""Marketing"""
        .InsertLines x + 4, vbTab & vbTab & ".addItem ""Sales"""
        .InsertLines x + 5, vbTab & vbTab & ".addItem ""Finance"""
        .InsertLines x + 6, vbTab & vbTab & ".addItem ""Research & Development"""
        .InsertLines x + 7, vbTab & vbTab & ".addItem ""Human Resources"""

        .InsertLines x + 8, vbTab & "End With"
        .InsertLines x + 9, "End Sub"
            
        ' write a procedure to handle the Cancel button
        '.InsertLines x + 1, "Private Sub cmdCancel_Click()"
        '.InsertLines x + 2, vbTab & "Unload Me"
        '.InsertLines x + 3, "End Sub"
        
        Dim firstLine As Long
        With objVBComp.CodeModule
             firstLine = .CreateEventProc("Click", "cmdCancel")
            .InsertLines firstLine + 1, "    Unload Me"
        End With
        
        ' write a procedure to handle OK button
        strCode = "Private Sub cmdOK_Click()" & vbCrLf
        strCode = strCode & "    Dim ctrl As Control" & vbCrLf
        strCode = strCode & "    Dim chkflag As Integer" & vbCrLf
        strCode = strCode & "    Dim strMsg As String" & vbCrLf
        strCode = strCode & "    If Me.cboDept.Value = """" Then " & vbCrLf
        strCode = strCode & "       MsgBox ""Please select the Department.""" & vbCrLf
        strCode = strCode & "       Me.cboDept.SetFocus " & vbCrLf
        strCode = strCode & "       Exit Sub" & vbCrLf
        strCode = strCode & "    End If" & vbCrLf
        strCode = strCode & "    For Each ctrl In Me.Controls " & vbCrLf
        strCode = strCode & "       Select Case ctrl.Name" & vbCrLf
        strCode = strCode & "         Case ""chk1"", ""chk2"", ""chk3""" & vbCrLf
        strCode = strCode & "           If ctrl.Value = True Then" & vbCrLf
        strCode = strCode & "             strMsg = strMsg & ctrl.Caption & Chr(13)" & vbCrLf
        strCode = strCode & "             chkflag = 1" & vbCrLf
        strCode = strCode & "           End If" & vbCrLf
        strCode = strCode & "       End Select" & vbCrLf
        strCode = strCode & "    Next" & vbCrLf
        strCode = strCode & "    If chkflag = 1 Then" & vbCrLf
        strCode = strCode & "      MsgBox ""Run the following Report(s) for "" & _ " & vbCrLf
        strCode = strCode & "      Me.cboDept.Value & "":"" & Chr(13) & Chr(13) & strMsg" & vbCrLf
        strCode = strCode & "    Else" & vbCrLf
        strCode = strCode & "      MsgBox ""Please select Report type.""" & vbCrLf
        strCode = strCode & "    End If" & vbCrLf
        strCode = strCode & "End Sub"

        .AddFromString strCode
  
    End With
End Sub