Need to call Listbox in a subroutine

[manney]

I am loading a listbox ("Listbox1") by name in the main body of code. I want to be able to do it in a subprogram, and I cannot seem to get it to work.

Sample code--

'Load Listbox
For iRow = 2 To iListLength + 1
Listbox1.AddItem (Sheets("Sheet1").Range(CallRange).Value)
Next iRow

iListLength is the number of items in the listbox
The listbox items themselves are on a worksheet (Sheet1, CallRange)

How might this code be modified so it can be called as a subprogram? I'd need to pass the name of the listbox as a parameter, plus (of course) sheet and range names.

Thank you,
pmanney@oriuscorp.com

 

[Juan Pablo González]

Hi, you can try something like this. As long as you pass the full range object, you don't need to specify the sheet's name.

<pre>Option Explicit

Private Sub FillLB(LB As MSForms.ListBox, WithRange As Range)
LB.Clear
LB.List = WithRange.Value
End Sub

Sub Test()
FillLB Sheet1.ListBox1, Sheet1.Range("A2:A6")
FillLB Sheet1.ListBox1, Sheet1.Range("B2:B4")
FillLB Sheet1.ListBox2, Sheet2.Range("D2:C9")
FillLB Sheet1.ListBox2, Sheet2.Range("D2:D7")
End Sub</pre>

 

[manney]

I won't get a chance to try it until tomorrow-- but thank you for your quick response!

P Manney

 

[manney]

Apologies-- I forgot to mention that the listbox is on a user form.

As I understand it, I cannot use the MSFormx.xxx for a listbox on a user form.

Thank you,
pmanney@oriuscorp.com

 

[Juan Pablo González]

Acutally, MSForms controls ARE the ones that you use on Userforms. So, did you try the code ?

 

[manney]

Thank you. I substituted "UserForm1" for "Sheet1" and got it to load properly.

This gives me the basic idea: thanks for your help.

Now, I'll try to figure out how to pass a choice to the listbox.

Thanks again,
pmanney@oriuscorp.com