Samples That Created Using VBA Class Modules

Hiding-Unhiding Worksheet Columns Using Userform

We created a different template by creating our own class in the VBA Window.In this template, a userform automatic load when the workbook is opened .There are a drop down list, buttons, checkboxes in this form.

The workbook’s sheets are listed in the drop down list.The sheets of the workbook can be navigated through this drop-down list. The used columns in each worksheet are lined up with their headers in the userform.

A checkbox for each column is created by the procedure in the module.When any checkbox is selected, the column corresponding to that checkbox is hidden by the procedure in the class module. Vba codes of module in userform :

Dim Buton1() As New Class_Controls
Sub auto_open()
Frm_Controls.Show
End Sub
Sub op()
Dim sayi As Integer
Dim ctrl As Control
    sayi = 0
    For Each ctrl In Frm_Controls.Controls
        If TypeName(ctrl) = "CheckBox" Then
            sayi = sayi + 1
            ReDim Preserve Buton1(1 To sayi)
            Set Buton1(sayi).fd = ctrl
        End If
    Next ctrl
End Sub

Codes of class module :

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

View of the userform in the Vba window :

If the number of checkboxes added for each the used column exceeds the width of the userform, a scrollbar is automatically created for the userform.

Our codes in UserForm_Initialize procedure that provide us with this feature :
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

All codes of the Userform_Initialize procedure with explanations :

Private Sub UserForm_initialize()
Dim son_sutun, j As Long
Dim sht As Integer
With Me
.Left = 0        'The location of userform on the screen is set.                   
.Top = 0
End With

For sht = 1 To ThisWorkbook.Worksheets.Count    'The sheets of workbook is added to combobox.
ComboBox1.AddItem Sheets(sht).Name
Next sht

ComboBox1.Value = ActiveSheet.Name

son_sutun = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column             'The used columns in the sheet are determined.
For i = 1 To 1                       'Creating check boxes
For j = 1 To son_sutun
  
    Set chkBox = Frm_Controls.Controls.Add("Forms.CheckBox.1", "CheckBox" & j)
With chkBox               'Features of the created checkboxes       
  .Top = i * 19
  .Left = (j * 70) - 65
  .Font.Size = 11
  .Caption = Split(ActiveSheet.Cells(1, j).Address, "$")(1) & " " & "-" & Cells(1, j).Value
End With
    chkbx_width = (son_sutun * 70) + 15
   
    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

Call op
End Sub

Another template created using the VBA class module :

Percantage calculate on userform – other template created using the VBA class module :

As seen in Excel userforms above, operations (percentage finding, sum etc.) are performed automatically without pressing any button. Class module codes of the template above :

Public WithEvents txt As MSForms.TextBox
Private Sub txt_Change()
    ToplamAliver
End Sub
Private Sub ToplamAliver()
On Error Resume Next
Dim nesne As Control
Dim Top1 As Double, Top2 As Double
Dim Ad As String
Dim No As Integer
With UserForm1

For Each nesne In .Controls
       If TypeName(nesne) = "TextBox" Then
            No = Right(nesne.Name, Len(nesne.Name) - 7)
            Ad = "TextBox"
                Select Case No
                   Case 1 To 3
                   Top1 = Top1 + VBA.Format(.Controls(Ad & No).Value, "#.00")
                   If .Controls(Ad & No).Value = "" Then
        .Controls(Ad & No + 4).Value = ""
       End If
                   .Controls(Ad & No + 4).Value = VBA.Format(.Controls(Ad & No).Value * (.TextBox9.Value / 100), "#0.00")
                    
                    Case 5 To 7
                         Top2 = Top2 + VBA.Format(.Controls(Ad & No).Value, "#.00")
                End Select
    End If
    Next
.TextBox4 = VBA.Format(Top1, "#.00")
.TextBox8 = VBA.Format(Top2, "#.00")

End With
End Sub

Userform’s view and codes of userform in VBA editor window :

📥 All sample files can be downloaded here