Excel Hide-Unhide Columns Via Userform

Excel VBA Hide-Unhide Columns By Userform

It can be difficult to work on multi column sheets that overflow from screen as horizontal, so we try to fit the sheet view to the screen by hiding some columns. We have prepared a userform to make it easy to hide and unhide worksheet columns.

✔ Firstly, we added a combobox (drop down list) at the top of the userform to list the workbook’s sheets. We added the following codes to the UserForm_Initialize procedure to list the workbook’s sheets on the combobox : ↓

Dim syf As Integer
For syf = 1 To ThisWorkbook.Worksheets.Count
ComboBox1.AddItem Sheets(syf).Name
Next syf

If the columns of the selected sheet from the combobox contain data are listed on the listbox in the userform with column headings. Listing all columns in the listbox causes the userform to run very slowly. We found the column no of the last column that contained data as follows and assign it to the variable lst_column : ↓

lst_column = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

We used the VBA For-Next loop to list the columns of the worksheet on the userform listbox : ↓

For sut = 1 To lst_column
ListBox1.AddItem Split(Sheets(ComboBox1.Value).Cells(1, sut).Address, "$")(1) & " " & " " & "-" & -
    Cells(1, sut).Value
If Sheets(ComboBox1.Value).Columns(sut).Hidden = True Then
ListBox1.Selected(sut - 1) = True
End If
Next

✔ We set the listbox ListStyle property to fmListStyleOption in the VBA Window Listbox1 Properties section to facilitate the selection process of the items (columns) listed in the listbox. Also, by selecting the fmMultiSelectMulti property for the listbox MultiSelect option, we have enabled multiple selection of listbox items ,so we can hide multiple columns.

✔ We added the following codes to the ListBox1_Change procedure to hide the column selected from the Listbox : ↓

Dim gizle As Integer
For gizle = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(gizle) = True Then
ActiveSheet.Cells(1, Split(ListBox1.List(gizle, 0))(0)).EntireColumn.Hidden = True
Else
ActiveSheet.Cells(1, Split(ListBox1.List(gizle, 0))(0)).EntireColumn.Hidden = False
End If
Next

✔ The userform that we created to hide-unhide columns is automatically displayed when the workbook is opened ,it placed in the upper right corner of workbook. We added the following codes to Module1 so that the userform is loaded automatically when the workbook is opened : ↓

Sub auto_open()
hide_unhide_Frm.Show
End Sub

We added the following Declare statements into userform’s codes to add minimize button on userform top : ↓

#If VBA7 Then
Private Declare PtrSafe Function FindWindowA Lib "user32" _
        (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare PtrSafe Function GetWindowLongA Lib "user32" _
        (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function SetWindowLongA Lib "user32" _
        (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#Else
Private Declare Function FindWindowA Lib "user32" _
        (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLongA Lib "user32" _
        (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLongA Lib "user32" _
        (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#End If

Private Sub UserForm_Activate()
    Dim hWnd As Long, exLong As Long
    hWnd = FindWindowA(vbNullString, Me.Caption)
    exLong = GetWindowLongA(hWnd, -16)
    If (exLong And &H20000) = 0 Then
        SetWindowLongA hWnd, -16, exLong Or &H20000
        Me.Hide
        Me.Show
    End If
End Sub

You can easily add own Excel workbook this userform and can use it. For this : Close userform .
→ Press Alt +F11 keys to open VBA Window .
→ Open your own workbook .
→ Drag Module1 and hide_unhide_Frm userform in this template to the part of your own workbook .
→ Save changes and restart your workbook.

Result : ↓