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

Remove what you have in RowSource in Properties.

You can do that manually or use the below code to do it.

Private Sub UserForm_Initialize()

    Me.ListBox1.RowSource = ""

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

End Sub

 

[bob6pp]

Norie,
I seem to have lost your latest reply, please can you resend.
Thanks
Bob

 

[bob6pp]

Norie,
I seem to have lost your latest reply, please can you resend.
Thanks
Bob

 

[bob6pp]

Hi Norie,
Sorry found your reply.
I've adapted it:-

Private Sub UserForm_Initialize()

Me.ListBox1.RowSource = "groups!A2:A5"

End Sub

This works just like I want.

This also works:-
Private Sub UserForm_Initialize()

enndd = 10

Me.ListBox1.RowSource = "groups!A2:A" & enndd

End Sub

However, please can you help with the next problem.
How do I pass a variable to this sub-routine, as the 'enndd' (variable) needs to change depending on the list lenght. I can calculate the list length in the main body of the code, and need this value to set the end point of my list.
Hope that makes sense.

I appreciate your time and effort in helping me
Thanks
Bob

 

[Norie]

Bob

Why not use the code I posted?

It will calculate the last row in the column and populate the listbox accordingly.

No need to pass any variable.

 

[bob6pp]

Well the reason is that I get an error message:-
Run-time error 381:-
Cannot set the List property. Invalid property array index.

 

[Norie]

Bob

There's a slight typo in the code I posted, this is how it should be.

Private Sub UserForm_Initialize()

    Me.ListBox1.RowSource = ""

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

End Sub

 

[bob6pp]

Hi Norie,
That certainly was a subtle typo, took me quite a while to spot it.

However your code works fine, and does exactly what I want. Many thanks.

Still not too sure how it works however.

Regards
bob