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 :