Use CheckBox captions as search terms to show columns

[AmyK023]

Excel 2010

I'm trying to write a macro for the command button below.

I have a Userfrom with ~90 check boxes and 2 CommandButtons.

Result wanted:
When the ShowSelection CommandButton is clicked, the columns containing the same headings as the Checked Checkboxes' captions is shown, while the remaining columns in range E to CS remain hidden.

As far as I can tell, my code is not actually finding the checkboxes, or at least isn't finding an active one before it faults out.

Tweaks, robustness, and suggestions are all requested.


Private Sub ShowSelection_Click()


Range("E32:CS32").Columns.Hidden = True


Dim CB As Control 'CB = checkbox
Dim AR As String
AR = "AssignRoles"
For Each CB In Me.Controls

If TypeName(CB) = "CheckBox" Then

'^based on other code I've seen, this, theoretically, finds only my check boxes​

If CB.Value = True Then

'^If the box is checked​

Dim KeepItem As String​

KeepItem = ActiveControl.Caption​

'^the first time this text is active, it returns the caption for one of the CommandBoxes on my userform, not the text from a checkbox​

Worksheets(AR).Range("E32:CS2").Select​

​

Selection.Find(What:=KeepItem, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _​

xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _​

False, SearchFormat:=False).Activate​

'^ My code runs to here, then I get "Run-Time error '91': Object Variable or With Block variable not set" and clicking debug highlights the "Find" lines​

On Error GOTo KeepGoing​

'^ Need to create option. Text should always be found. Need to test during debug. If text not found, then need to check captions and doc text​

​

ActiveCell.Columns.Hidden = False​

End If​

End If



KeepGoing:




Next


RoleSelection.Hide

End Sub

 

[erik.van.geit]

Hi,

Obviously the KeepItem is not found.
Your code tries to "activate" something which does not exist, hence the error message.

You could make sure that your checkboxes are really correctly spelled out, but I would not do it like that.
You can use a listbox and populate it with the column headers.
Set it to multiselect.
Your code will then be very easy.

    With ListBox1
        For i = 0 To .ListCount - 1
            Columns(i +1).Hidden = .Selected(i) = False
        Next i
    End With

(not tested, typos possible)
kind regards,
Erik

Erik

 

[AmyK023]

Erik -

Thank you for the response. As a I am new to Userform/Checkbox/VBA, I'm not sure that a listbox achieves the result I'm looking for. I've been attempting to create the listboxes, but am not seeing much success (it's safe to assume there is a high level of operator error there).

My userform takes a 90+ x 1 array and re-arranges it a 6 x ~15 array to allow the user to see all of the categories at once (not all users need all categories). Then, the user can (theoretically) click the check boxes to have a User-defined number of columns for data entry purposes.

As I understand it, I somehow need to tie a column to the checkbox and then write a script so that when the check box is checked, the column shows. I'm attempting to make the tie dynamic as I expect this document will be modified for several sites and multiple users.

Any additional assistance/advice is appreciated.

Cheers,
Amy

 

[erik.van.geit]

I suggest you try out the listbox on a copy of your workbook or you can quickly create a try out workbook. All you need is some column labels. Few work quick result.

Using the "controls" (not the "forms") panel create a listbox called "ListBox1"
Put this code within a normal module.

Option Explicit

Sub Populate_ListBox1()
Sheets("Sheet1").ListBox1.List() = Application.Transpose(Range("A1:Z1").Value) 'replace "A" and/or "Z" by any column label
End Sub

Sub ShowSelectedColumns()
Dim i As Long

    With Sheets("Sheet1").ListBox1
        For i = 0 To .ListCount - 1
            Columns(i + 1).Hidden = .Selected(i) = False
        Next i
    End With
End Sub

Run "Populate_ListBox1"
make some changes to your listbox
Run "ShowSelectedColumns"

No mess with a lot of controls
No mess with wrong header names

Do a real effort to make this work; it will spare you a lot of trouble if this looks like what you can use.

best regards,
Erik

 

[erik.van.geit]

I had another version of the code which was more elaborated.
Posted the wrong one in previous reply: it would work, but this one the following one is better.

Option Explicit

Sub Populate_ListBox1()
Dim LastCol As Long

    With Sheets("Sheet1")
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .ListBox1.List() = Application.Transpose(.Range(.Cells(1, 1), .Cells(1, LastCol)).Value)
    End With
End Sub

Sub ShowSelectedColumns()
Dim i As Long

    With Sheets("Sheet1").ListBox1
        For i = 0 To .ListCount - 1
        Columns(i + 1).Hidden = .Selected(i) = False
        Next i
    End With
    
    ActiveWindow.ScrollColumn = 1

End Sub

 

[AmyK023]

Erik -

Thanks again for the code and apologies for my long over due reply.

I was able to successfully incorporate your code into my document. After tinkering with the layout and conferring with other members of the group, we determined that a Listbox was not the correct tool for the application.

In the mean time, I did a data scrub of my headers and check boxes and have successfully created a pop-up userform with check boxes that, when executed, shows the columns corresponding to the checked boxes. Unfortunately, populating the check box captions is not as flexible as I like, but this is likely to be a one-time use form so dynamic text is not really necessary.

Regards,

Amy

 

[erik.van.geit]

You're welcome!
If possible you can send me the workbook to my email? I would be curious to see how it would be easier using textboxes: perhaps I did not understand entirely your question?

kind regards,
Erik

EDIT: just a little typo

 

[AmyK023]

See email with subject "Use CheckBox captions as search terms to show columns" to the email address hotlinked in your signature.

 

[erik.van.geit]

Hi,

I've checked out your workbook. My choice would still be a listbox for each category.

The userform is more easy to edit.
One day when something changes (and it will soon or later) then it's easy to edit some codelines like

ListBox1.List() = Array(1, 2, "other item", 4, 5, 6)

, instead of adding or deleting checkboxes.
The items in the listboxes would stay visible
With a few lines of code the height of the listboxes would be set depending of the number of items.

beste regards,
Erik