Hide columns based on userform selection?

[tinkythomas]

Hi,

I have a userform with a multiselect listbox with checkboxes. These checkboxes represent the columns in the activesheet.

I have written code to populate the listbox with the used column headers in the activesheet. The listbox takes these values from an array.

My question is how do I get the checkboxes selected and then hide the applicable columns on the activesheet?

The code I have so far...

Option Explicit
Option Base 1

Private Sub UserForm_Initialize()
    Dim rng As Range
    Dim i As Integer, _
        lc As Integer
    Dim arr() As String

    lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set rng = ActiveSheet.Range(Cells(1, 1), Cells(1, lc))

    For i = LBound(rng.Cells.Value2, 2) To UBound(rng.Cells.Value2, 2)
        ReDim Preserve arr(1 To i)
        arr(i) = rng.Cells.Value2(1, i)
    Next i

    ListBox1.List = arr

    Set rng = Nothing
End Sub

Any help would be appreciated.

Regards,

 

[usenethelp]

You will need to add the following one line to your existing code so that it does not error out if columns are already hidden:
Range("1:1").EntireColumn.Hidden = False

If you want to hide the unchecked columns and display the checked ones you can use the following (just swap the actions or False with a True if you want it the other way around). Caution: this only currently works if your headings are text:

Private Sub ListBox1_Change()
Dim iCurrent As Long
For iCurrent = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCurrent) = False Then
ActiveSheet.Cells(1, WorksheetFunction.Match(Me.ListBox1.List(iCurrent), ActiveSheet.Range("1:1"), 0)).EntireColumn.Hidden = True
Else
ActiveSheet.Cells(1, WorksheetFunction.Match(Me.ListBox1.List(iCurrent), ActiveSheet.Range("1:1"), 0)).EntireColumn.Hidden = False
End If
Next iCurrent
End Sub

 

[tinkythomas]

Thanks usenethelp for the code. Can this be achieved by column index rather than column header text?

Thank you for your help!

 

[usenethelp]

Good idea!

Change:
ActiveSheet.Cells(1, WorksheetFunction.Match(Me.ListBox1.List(iCurrent), ActiveSheet.Range("1:1"), 0)).EntireColumn.Hidden = True

to:
ActiveSheet.Cells(1, iCurrent).EntireColumn.Hidden = True

 

[tinkythomas]

Sorry, I could have figured that out myself! Sorry to have asked the question!

Thank you very much for the code, it really is appreciated.