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