Trying to make Listbox UserForm to hide Chosen Columns

[Elizardbreath]

Hi this is my first post and I'm a excel vba beginner. I tried to find other relevant threads that would help me with this challenge but I was unable to apply it correctly to my situation.

I am trying to make a UserForm for the first time. The goal of my userform is to take the headers of each column and create a listbox which the user can then multiselect columns to be hidden.

I succeeded in the first part by writing a sub procedure to copy the column headers and pastespecial(transpose) into the next unused column and then select that column and make it a named range. I put that named range as "rowsource" for the listbox and everything looked nice. I changed the multiselect option to extended.

I've added an OK button and a Cancel button. I'm trying to write the code for the OK button to deduce which entries in the listbox are selected and ultimately hide the columns that those headers correspond to.

I've tried a couple of different approaches by pillaging code from books and forums but to no success. My main issue is I cannot figure out how to take the listbox selections and link back to the columns. Here's my code so far and the main error seems to be coming from the ".find" section:

Private Sub Ok_Click()
    Dim i As Long
    Dim X As Long
    Dim lastrow As Long
    Dim lastcol As Long
    Dim value As String

lastcol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
For i = 0 To ColumnList.ListCount - 1
    If ColumnList.Selected(i) = True Then
    lastrow = Cells(Rows.Count, lastcol).End(xlUp).Row + 1
    Cells(lastrow, lastcol).value = ColumnList.List(i)
    End If
Next i

For X = 2 To lastrow
    value = Cells(X, lastcol).value
    If value <> vbNullString Then
    Cells.Find(what:=value, after:=Range("A1"), LookIn:=xlFormulas, _
    lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).EntireColumn.Hide = True
    End If
Next X

Unload Me
End Sub

I suspect there are multiple ways to complete this goal but I am trying to use the tools that I am familiar with. I read about transferring the list to an array but it was my first attempt at working with arrays and I couldn't apply it. This was the thread I was trying to work with before resorting to writing the listbox selection back onto the worksheet.search string array listbox

I would ideally like to get away from writing the listbox contents onto the worksheet which is called "Combined RSFs" but I can always just delete it at the end. Also it would be nice to be able to use the column headers themselves in the listbox instead of my current approach of copying them into a new column and naming that range "Headers". This is also something I could delete from the sheet at the end too though.

Thanks for your assistance and there is no rush in solving this question, I'm just working on it for fun.

 

[Jerry Sullivan]

Hello and Welcome,

If you have headers with no blanks from Cell A1 to your last column, then you can just use the selected index numbers plus 1 to reference the columns you want to hide. Something like...

Private Sub UserForm_Initialize() 'Fill list
    Dim i As Long, lastcol As Long
    lastcol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To lastcol
        ColumnList.AddItem ActiveSheet.Cells(1, i)
    Next i
End Sub
 
Private Sub OK_Click() 'Hide Selected Columns
    Dim i As Long
    For i = 0 To ColumnList.ListCount - 1
        If ColumnList.Selected(i) = True Then
            ActiveSheet.Columns(i + 1).Hidden = True
        End If
    Next i
    Unload Me
End Sub

You could modify this if you are skipping blank columns or starting in a column other than Col A.

 

[Elizardbreath]

Thanks Jerry!

That's exactly what I was striving for. Thank you for your help

Elizabeth