Monday, October 10, 2016

Excel Dynamically Adding Controls To Userform - Task Assignment To Controls

           In this tutorial , check boxes are automatically created based on the used column count when userform opens. The created check boxes are sorted horizontally at regular intervals :

"lst_column = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To 1                                   'Creating check boxes
For j = 1 To lst_column
    Set chkBox = Frm_Controls.Controls.Add("Forms.CheckBox.1", "CheckBox" & j)
    With chkBox
        .Top = i * 18
        .Left = (j * 70) - 65
        .BackColor = vbGreen
        .Font.Size = 11
        .Caption = Split(ActiveSheet.Cells(1, j).Address, "$")(1) & " " & "-" & Cells(1, j).Value
    End With
    chkbx_width = (lst_column * 70) + 15
    'MsgBox chkbx_width
    If chkbx_width > Me.InsideWidth Then
    With Me
    .ScrollBars = fmScrollBarsHorizontal           'This will create a horizantal scrollbar
    .ScrollWidth = chkbx_width + 50
     End With
     Else
     Me.ScrollBars = fmScrollBarsNone
     End If
Next j
Next i
.."



Check boxes are rearranged (they are removed and recreated) depending on the selected worksheet from the drop-down list :

"For Each ctl In Frm_Controls.Controls                    'Removing old check boxes
        If TypeName(ctl) = "CheckBox" Then
            Frm_Controls.Controls.Remove ctl.Name
        End If
    Next ctl
.."

Column hiding-unhiding tasks are appointed to the check boxes :

"Public WithEvents fd As MSForms.CheckBox
Private Sub fd_Click()
Dim a As Integer
If fd.Value = True Then
a = Replace(fd.Name, "CheckBox", "")
Sheets(Frm_Controls.ComboBox1.Value).Cells(1, a).EntireColumn.Hidden = True
Else
a = Replace(fd.Name, "CheckBox", "")
Sheets(Frm_Controls.ComboBox1.Value).Cells(1, a).EntireColumn.Hidden = False
End If
End Sub
"
You can easily add own excel file this userform and can use it. For this :
- Close userform .
- Press Alt +F11 keys to open VBE (Visual Basic Editor) Window .
- Open your own file .
- Drag module,class and userform in this template to the part of your own files .
- Save changes and restart your file.

No comments:

Post a Comment