Excel Hide & Unhide Columns With Listbox

Hide & Unhide Columns With Listbox


          When you click the button on the sheet, the userform is loaded.
The userform contains a listbox and a combobox control. In the listbox, columns of sheet are listed. Next to the listbox items (names of columns) are check boxes for selection.

The selected column or columns from the listbox are hidden. Multiple selections can be made from the listbox. The userform can be used on all worksheets of the workbook. With the combobox on the userform can be navigated between sheets.

excel hide columns


        In the previous template, we listed all the columns of the sheet in the listbox. Therefore, the process of hiding the columns was slow.
We made changes to Vba codes and only listed the used columns (ActiveSheet.UsedRange.Columns) in the new template to make the procedure run fast.

excel vba hide columns

We added a standard minimize button to the userform to facilitate use with that codes : 
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

 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

We have added the following codes to the listbox_change method to hide the columns:
Private Sub ListBox1_Change()                 'The columns that selected on listbox are hidden.
Dim gizle As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If yukleme = "ok" Then
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
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub


No comments:

Post a Comment