Hide columns based on userform selection?

tinkythomas

Active Member
Joined
Dec 13, 2006
Messages
432
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...
Code:
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,
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

usenethelp

New Member
Joined
Dec 3, 2009
Messages
33
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

Active Member
Joined
Dec 13, 2006
Messages
432
Thanks usenethelp for the code. Can this be achieved by column index rather than column header text?

Thank you for your help!
 

usenethelp

New Member
Joined
Dec 3, 2009
Messages
33
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

Active Member
Joined
Dec 13, 2006
Messages
432
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,258
Messages
5,600,567
Members
414,389
Latest member
MarkElla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top