Initialize list box

[bob6pp]

Hi,
I very new to using boxes etc.
I have been able to create a list box, that takes it's data from a page in the main spreadsheet. The relevant line in the properties is 'rowsource=groups!A2:A15'. I want to make that more dynamic so that I can set it to 'say':- =groups!A2:A20, when the program runs.
From my investigations, I think I need to initialse the list box, and pass the text ie:- '=groups!A2:A20' to it somehow
Please can some show me how to do this, and where the code actually lives?

Thanks
Bob

 

[Norie]

Bob

Where is the listbox located?

 

[bob6pp]

In the middle of User form

 

[Norie]

Bob

How should the population of the listbox be more 'dynamic'?

 

[bob6pp]

The list of items is defined on a sheet called 'groups'. At the moment they are all known about, but in the future the list might get longer. I want to write some code that tracks the list of groups, and increases as it gets longer, or in if it get shorter

 

[Norie]

You can populate the listbox in the userform's Initialize event

Private Sub UserForm_Initialize()

    With Sheets("Groups")
        Me.ListBox1.LIst = .Range("A2", .Range("A" & Rows.Count)).End(xlUp).Value
    End With

End Sub

 

[bob6pp]

Hi Norie,
Thanks for that.
I don't really understand your code, but before I try to see how it works, where exactly should this code be.
Should it be within the main macro? or where.
Please advise.
Thanks
Bob

 

[Norie]

It would go in the userform module.

 

[bob6pp]

 

[bob6pp]

Hi Norie,
Like in the examples above?

I single stepped your code and get an error message:-
Run-time error '381':
Could not set the List Property. Invalid property array index.

Where am going wrong.
Thanks
Bob