Add multiple checkbox in vb form on runtime

I have a spreadsheet with a list of values in column A that I want to
show up as checkboxes on a userform. The current code crashes when it
tries to add a caption to the checkbox. I get a runtime error "Could
not find the specified object"
The actual code would call a function to determine how many rows in
column A which will vary, and I'll add code to size the form and
manipulate the checkboxes to fit.

Private Sub UserForm_Initialize()
Dim iRow As Integer
Dim iLeft As Integer
Dim ctlCheckBox As Control
Dim sName As String
Dim iNumRows As Integer
Dim iTop As Integer

Worksheets("Hour Per Equipment").Activate
iTop = 10
For iRow = 3 To iNumRows
If Cells(iRow, "A") <> "" Then
Set ctlCheckBox = frmEqpDetails.Controls.Add("frmEqpDetails.VisChkbox.1", "cb" & iRow)
sName = "cb" & iRow

'I tried using ctlCheckBox sub for sName-same result

frmEqpDetails.Controls(sName).Caption = Cells(iRow, "A")
Me.Controls(sName).Left = iLeft
ctlCheckBox.Top = iTop
iTop = iTop + 10
End If
Next iRow
End Sub

Hi there,

Try the following code and see if it does what you need:

Private Sub UserForm_Initialize()

    Const iMAX_CONTROLS As Integer = 20
    Const sSHEET_NAME   As String = "Hour Per Equipment"
    Const iFIRST_ROW    As Integer = 3
    Const iFIRST_TOP    As Integer = 12
    Const iPITCH        As Integer = 24
    Const iLEFT         As Integer = 12

    Dim ctlCheckBox     As MSForms.Control
    Dim iTop            As Integer
    Dim iRow            As Integer
    Dim wks             As Worksheet

    Set wks = ThisWorkbook.Sheets(sSHEET_NAME)

    iTop = iFIRST_TOP

    For iRow = iFIRST_ROW To (iFIRST_ROW + iMAX_CONTROLS - 1)

        If wks.Cells(iRow, "A") <> vbNullString Then

            Set ctlCheckBox = Me.Controls.Add("Forms.Checkbox.1")

            With ctlCheckBox

                .Caption = Cells(iRow, "A")
                .Name = "cb" & iRow
                .Left = iLEFT
                .Top = iTop

            End With

            iTop = iTop + iPITCH

        End If

    Next iRow

End Sub

The highlighted values can be changed to suit your own worksheet and UserForm layout requirements.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg,

How can I use this code in multipage form.

Please find the attached file.

The problem is that The code which you had shared is working properly. But, with multipage form its not as per the requirement.

Hi MOHAMMESALMAN,

See the attached sample file that creates UserForm CheckBoxes dynamically. It also uses a Class Event Handler that can process all CheckBox Events, instead of having to add one for each CheckBox in the UserForm module.

Instructions for creating a Class Event Handler:
a. To prevent a compile error, in the VBA Editor add the following library reference:
Tools -> References -> Microsoft Forms 2.0 Object Library (Excel 2003 version)

b. Create Class ClassCheckBox
The following line is required at the top of the Class:
Public WithEvents CheckBoxGroup As MSForms.CheckBox
There are several built in events that can be selected.

c. Put the following line at the top of an ordinary Code module:
Public myCheckBoxes() As New ClassCheckBox

d. Put the following calls in UserForm_Initialize:
(1) - Call CreateUserForm1DynamicCheckBoxes 'To CREATE Dynamic (created at runtime) controls
(2) - Call GenerateUserForm1CheckBoxControlArray 'To assign CheckBoxes to the CheckBoxGroup
Controls in the CheckBoxGroup will have events handled by ClassCheckBox
NOTE: If a UserForm Event exists, it will still be activated before the Class Event.

Class ClassCheckBox code. NOTE if the Class name is changed, other items in the code must be changed also.

Option Explicit

Public WithEvents CheckBoxGroup As MSForms.CheckBox

Private Sub CheckBoxGroup_Click()
  'This is the Event Handler for CheckBox Click Events caused by CheckBoxes in the CheckBoxGroup
    
  If Not bGblInhibitCheckBoxEvents Then
  
    'Increment the CheckBox 'Click' Counter
    iGblCheckBoxClickCount = iGblCheckBoxClickCount + 1

    UserForm1.LabelStatus.Caption = _
      "Count = " & iGblCheckBoxClickCount & _
      "   CheckBox Click Event from " & CheckBoxGroup.Name & "  Value = " & CheckBoxGroup.Value
        
    Call ProcessUserForm1CheckBoxgroupClickEvent(CheckBoxGroup.Name)
        
  End If
    
End Sub

UserForm1 module code:

Option Explicit

Private Sub UserForm_Initialize()

  'Initialize the CheckBox Click Counter
  iGblCheckBoxClickCount = 0

  'Create Dynamic (i.e. created at runtime) CheckBoxes
  Call CreateUserForm1DynamicCheckBoxes
    
  'Create the Control Array for CheckBoxGroup CheckBox Events
  Call GenerateUserForm1CheckBoxControlArray
    
End Sub

In an ordinary code module such as Module1:

Option Explicit

Public myCheckBoxes() As New ClassCheckBox
Public bGblInhibitCheckBoxEvents As Boolean
Public iGblCheckBoxClickCount As Long

Sub DisplayUserForm1()
    'UserForm1.Show vbModal    'This locks out Excel - probably best choice for PRODUCTION
    UserForm1.Show vbModeless  'This allows access to the Spreadsheet - best choice for DEBUGGING
End Sub

Public Sub CreateUserForm1DynamicCheckBoxes()
  'This generates the  'CheckBox' matrix for UserForm1

  Const MAX_UserForm1_CHECKBOX_ROWS = 3
  Const MAX_UserForm1_CHECKBOX_COLS = 2
  Const MAX_UserForm1_CHECKBOX_CONTROLS = MAX_UserForm1_CHECKBOX_ROWS * MAX_UserForm1_CHECKBOX_COLS
  
  Const nLeftOffset = 4
  Const nTopOffset = 150
  
  Const nCellHeight = 18
  Const nCellWidth = 180
  
  Const nRowSpace = 5
  Const nColSpace = 10

  Dim cCntrl As Control
  Dim iCol As Integer
  Dim iRow As Integer
  Dim sGroupName As String
  Dim sName As String
  

  
  For iRow = 1 To MAX_UserForm1_CHECKBOX_ROWS
    
    For iCol = 1 To MAX_UserForm1_CHECKBOX_COLS
  
    
      ''''''''''''''''''''''''''''''''''''''''''''
      'Create CheckBox
      ''''''''''''''''''''''''''''''''''''''''''''
      sName = "CheckBoxDynamic" & Format(iRow, "00") & Format(iCol, "00")
      sGroupName = "GroupDynamic" & iCol
      Set cCntrl = UserForm1.Controls.Add("Forms.CheckBox.1", sName, True)
      
      With cCntrl
        .Name = sName
        .Caption = sName
        .Width = nCellWidth
        .Height = nCellHeight
        .Top = nTopOffset + (nCellHeight + nRowSpace) * (iRow - 1)
        .Left = nLeftOffset + (nCellWidth + nColSpace) * (iCol - 1)
        .ZOrder (0)     '0/1 Order on top/bottom
      End With

    Next iCol

  Next iRow

End Sub


Sub GenerateUserForm1CheckBoxControlArray()
  'This creates the array of CheckBoxes processed by the 'ClassCheckBox' Event Handler

  Dim cCntrl As Control
  Dim sName As String
  Dim sTypeName As String
  
  Dim iCheckBoxIndexMax As Integer
  
  iCheckBoxIndexMax = 0
  ReDim myCheckBoxes(1 To 1)
  For Each cCntrl In UserForm1.Controls
              
    sTypeName = TypeName(cCntrl)
    
    If sTypeName = "CheckBox" Then
    
      'Get the Control Name
      sName = cCntrl.Name
        
      'Debug.Print "CheckBox Name " & sName
      iCheckBoxIndexMax = iCheckBoxIndexMax + 1
            
      ReDim Preserve myCheckBoxes(1 To iCheckBoxIndexMax)
      Set myCheckBoxes(iCheckBoxIndexMax).CheckBoxGroup = cCntrl
    End If

  Next cCntrl
   

End Sub


Sub ProcessUserForm1CheckBoxgroupClickEvent(sControlName As String)
  'This Processes Click Events caused by CheckBoxes in the CheckBoxGroup

  'Currently a Stub

End Sub

Lewis

Hi again,

Take a look at the attached workbook - it shows how to implement your requirements when a Multipage is involved. It takes Test Categories stored in a named range and assigns them to the Multipage tabs:

Private Sub UserForm_Initialize()

    Const sCATEGORIES   As String = "tblTestCategories"
    Const sSHEETNAME    As String = "Sheet1"

    Dim rCategories     As Range
    Dim iCellNo         As Integer
    Dim rCell           As Range

    Set rCategories = ThisWorkbook.Sheets(sSHEETNAME).Range(sCATEGORIES)

    With Me.MultiPage1

        .Pages(0).Caption = rCategories.Cells(1, 1).Value
        .Pages(1).Caption = rCategories.Cells(2, 1).Value

        For iCellNo = 3 To rCategories.Cells.Count
            .Pages.Add "Page" & iCellNo - 1, rCategories.Cells(iCellNo, 1).Value
        Next iCellNo

    End With

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M