+ Reply to Thread
Results 1 to 5 of 5

Add multiple checkbox in vb form on runtime

Hybrid View

  1. #1
    Registered User
    Join Date
    07-14-2015
    Location
    India
    MS-Off Ver
    3
    Posts
    24

    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

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,645

    Re: Add multiple checkbox in vb form on runtime

    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

  3. #3
    Registered User
    Join Date
    07-14-2015
    Location
    India
    MS-Off Ver
    3
    Posts
    24

    Re: Add multiple checkbox in vb form on runtime

    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.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Add multiple checkbox in vb form on runtime

    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
    Attached Files Attached Files

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,645

    Re: Add multiple checkbox in vb form on runtime

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Protect the sheet but the form checkbox still can be change and sort by checkbox
    By carolyn1221 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-19-2014, 01:08 PM
  2. Trying to scale a form checkbox in a macro, don't know how to get checkbox name
    By tstruch in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-09-2014, 12:14 AM
  3. Form Control Checkbox - multiple cell links?
    By brokenbiscuits in forum Excel General
    Replies: 2
    Last Post: 06-16-2011, 10:57 AM
  4. How to create CheckBox with event in runtime ?
    By bettatronic in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-02-2007, 05:54 PM
  5. [SOLVED] Runtime error with vba and checkbox
    By SplatterKat in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-08-2006, 08:10 PM
  6. checkbox in runtime
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-15-2006, 06:10 AM
  7. checkbox on form reset from checkbox on sheet
    By raw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-03-2005, 01:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1