Filling Listbox with checkbox information

[Boechat]

Hey everyone,

I have a worksheet where the information is displayed in the following manner: a table containing 3 columns:

A - List of objects
B - Categories
C - Comments

I have a userform in VBA that is built in the following manner:

4 checkboxes (corresponding to the categories in column B)
1 listbox
1 textbox

What I want it to do is:
I want the listbox to return the objects in column A, corresponding to the categories that have been checked in the Checkboxes!


Once that is running, I want that, upon selecting one item in the listbox, the textbox displays the comment contained in the third column of the corresponding line.

If it wasn't clear enough, please let me know!
Thanks in advance for your support!

 

[MickG]

Add this code to you Userform code Module.
NB:- You will need a "CommandButton1 " in Userform to run the code after selecting "CheckBoxes"
NB:- See Code Remarks regarding checkboxes and categories

Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Cat [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] CBox [COLOR=navy]As[/COLOR] Control
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))

ReDim ray(1 To Rng.Count, 1 To 2)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] CBox [COLOR=navy]In[/COLOR] Me.Controls
    [COLOR=navy]If[/COLOR] TypeName(CBox) = "CheckBox" [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] CBox = True [COLOR=navy]Then[/COLOR]
            [COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] CBox.Name
                Case "CheckBox1": Cat = "Cat1"  '[COLOR=green][B]Change "Cat1" to your actual Category Name[/B][/COLOR]
                Case "CheckBox2": Cat = "Cat2"  '[COLOR=green][B]Change "Cat2" to your actual Category Name[/B][/COLOR]
                Case "CheckBox3": Cat = "Cat2"  '[COLOR=green][B]Change "Cat3" to your actual Category Name[/B][/COLOR]
                Case "CheckBox4": Cat = "Cat4"  '[COLOR=green][B]Change "Cat4" Ditto  [/B][/COLOR]           
 [COLOR=navy]End[/COLOR] Select
            [COLOR=navy]If[/COLOR] Not .exists(Cat) [COLOR=navy]Then[/COLOR] .Add Cat, ""
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] CBox


[COLOR=navy]If[/COLOR] .Count > 0 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] .exists(Dn.Offset(, 1).Value) [COLOR=navy]Then[/COLOR]
            c = c + 1
            ray(c, 1) = Dn.Value
            ray(c, 2) = Dn.Address
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] With
[COLOR=navy]With[/COLOR] ListBox1
 .ColumnCount = 1
 .List = ray
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] ListBox1_Click()
[COLOR=navy]With[/COLOR] ListBox1
   TextBox1.Value = Range(.List(.ListIndex, 1)).Offset(, 2).Value
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Boechat]

It works perfectly!

Thank you a lot for your prompt answer!

 

[MickG]

You're welcome

 

[Boechat]

Last doubt:

The comments that are being populated into the textbox are very long. Is there a way for the textbox to automatically skip to a new line once it reaches the edge instead of scrolling?

Thanks!

 

[MickG]

Try any or all of these in the Lisbox_Click Event, see what suits.

Private Sub ListBox1_Click()
With ListBox1
   TextBox1.ScrollBars = fmScrollBarsVertical
   TextBox1.MultiLine = True
   TextBox1.WordWrap = True
   TextBox1.Value = Range(.List(.ListIndex, 1)).Offset(, 2).Value
End With

End Sub

 

[Boechat]

Works perfectly as intended!

Thank you!

[Thread Solved]

 

[MickG]

You're very welcome