Home > Excel Tutorial > Excel Dynamically Adding Controls (Checkbox) To Userform – Task Assignment To Controls

Excel Dynamically Adding Controls (Checkbox) To Userform – Task Assignment To Controls

        In this template , 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

Template can be used for the purpose of column hiding – column unhiding.

Click Here To Download Template

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: