Elizardbreath
New Member
- Joined
- Feb 3, 2011
- Messages
- 2
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:
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.
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:
Code:
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
