Populating a listbox in a userform with filter

[TheShaunMichael]

Column A of my worksheet has values beginning in row 12. There are "title" cells and then a series of "sub" cells. The title cells are bolded and slightly larger. With an If Then loop i'm planning on having excel see those bolded cells. What I can't figure out is how to take those bolded cells and put them into a listbox on a userform.

Please help! Thanks!

 

[Jon von der Heyden]

Hopefully this gives you the direction:

Loop through range.
If cell is bold then store the cell text in an array.
Fill the listbox with the array.

[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] UserForm_Initialize()
    [COLOR=blue]Dim[/COLOR] lngLastRow [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR], lngArrItem [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
    [COLOR=blue]Dim[/COLOR] rngCell [COLOR=blue]As[/COLOR] Range
    [COLOR=blue]Dim[/COLOR] arr()
    
    lngArrItem = -1
    [COLOR=blue]With[/COLOR] Sheet3 [COLOR=green]'change to refer to your sheet[/COLOR]
        lngLastRow = .Range("A" & Rows.Count).End(xlUp).Row
        [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] rngCell [COLOR=blue]In[/COLOR] .Range("A12:A" & lngLastRow)
            [COLOR=blue]If[/COLOR] rngCell.Font.Bold [COLOR=blue]Then[/COLOR]
                lngArrItem = lngArrItem + 1
                [COLOR=blue]ReDim[/COLOR] [COLOR=blue]Preserve[/COLOR] arr(lngArrItem)
                arr(lngArrItem) = rngCell.Text
            [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
        [COLOR=blue]Next[/COLOR] rngCell
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
 
    Me.ListBox1.List = arr [COLOR=green]'change to refer to your listbox[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[Jon von der Heyden]

Actually using AddItem method is neater in this instance:

[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] UserForm_Initialize()
 
    [COLOR=blue]Dim[/COLOR] lngLastRow [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
    [COLOR=blue]Dim[/COLOR] rngCell [COLOR=blue]As[/COLOR] Range
 
    [COLOR=blue]With[/COLOR] Sheet3 [COLOR=green]'change to refer to your sheet[/COLOR]
        lngLastRow = .Range("A" & Rows.Count).End(xlUp).Row
        [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] rngCell [COLOR=blue]In[/COLOR] .Range("A12:A" & lngLastRow)
            [COLOR=blue]If[/COLOR] rngCell.Font.Bold [COLOR=blue]Then[/COLOR]
                Me.ListBox1.AddItem rngCell.Text
            [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
        [COLOR=blue]Next[/COLOR] rngCell
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[TheShaunMichael]

The additem function works except it doesn't seem to update properly.

When I click the cancel commandbutton on my form the code is: me.hide

If I do this and delete one of the bolded cells, it still shows up in the listbox when I open the form again. What I discovered is if I click the close button on the top right corner of the userform it updates properly.

What code do I need to add to the on click cancel commandbutton so it wipes the listbox clean/doesn't remember what it was populated with?

 

[TheShaunMichael]

Okay I think I figured it out..rather than putting the code under the form_initialize function I placed it in the same module that opens the form...so it runs through the code and then says formtactic.show...seems to be working properly now. Any comments/concerns?

 

[Jon von der Heyden]

When I click the cancel commandbutton on my form the code is: me.hide

If you want to 'close' the form entirely then use;
Unload Me

 

[TheShaunMichael]

John,

You are wonderful! I forgot all about that bit of code - i'm fairly new at this. Although i'm beginning to realize that may be true for a long time!

As a generalization, where can I learn more about the variables you set up under the additem code. Things like "long" are still relatively foreign to me. I've gone through Mr.Excel's video VBA program which was tremendous. Is there something more advanced you can recommend?

 

[Jon von der Heyden]

Well I would always recommend a good book. If you are just starting then I recommend one of John Walkenbach's Power Programming books, depending on whcih excel version you are using. His examples variables are always explicitly declared and if I remember he does describe a few of the different types.

To see detail of the types look up 'Data Types' in VBA help, there should be a summary of the various.

Otherwise just hand around the various forums and check out other peoples code - and don't feel shy to ask questions