Populate a listbox from worksheet range... BUT do it with a BAS module call

[gymsheets]

Populate a listbox from worksheet range BUT do it with a BAS module call


Greetings-

I have a column of data (really several different columns of data) and I want to throw it into a userform listbox- not too hard.

The tricky part is that I want to be able to throw the code into a *.bas module, generalize it and be able to efficiently refer back to the code like this:


Call populatelb(WhatListbox,StartRange,EndRange)


The big problem is when I attempt to generalize the listbox populating function in a bas, if I dimension a variable as a listbox I *CANNOT* call it from above, I keep getting a type mismatch error. I have tried ****-near everything with no luck.


Below is what works and I would like to generalize it so I can call it as many times as I need to with different ranges and listboxes I specify.


Dim lb As MSForms.listbox


Dim rcArray() As Variant
Dim lrw As Long, lcol As Long
Dim rngTarget As Range

'Define the range you want to use
Set rngTarget = Worksheets("Sheet1").Range("A4:A50")



'Set the boundaries of the array
ReDim Preserve rcArray(1 To rngTarget.Rows.Count, 1 To rngTarget.Columns.Count)

'Fill the array with data from the worksheet
With rngTarget
For lcol = 1 To .Columns.Count
For lrw = 1 To .Rows.Count
rcArray(lrw, lcol) = rngTarget.Cells(lrw, lcol)
Next lrw
Next lcol
End With

'Place the array in the combobox
Set lb = Me.ListBox1
With lb
.List = rcArray
End With


Many, many thanks for your insights

 

[xld]

Try

Public Sub populatelb( _
    lb As MSForms.ListBox, _
    StartRange as range, _
    EndRange as range)
Dim rcArray As Variant
Dim rngTarget As Range

    'Define the range you want to use
    Set rngTarget = Range(StartRange, EndRange)
    
    'Fill the array with data from the worksheet
    rcArray = rngTarget
    lb.List = rcArray
End Sub

 

[gymsheets]

This works like a charm:

Call poplbcombo(ListBox3, "A1:A45")



Module:

Public Function poplbcombo(lbdata As msforms.listbox, b As String)
Dim rMyCell As Range
Set rMyCell = Range(b)


Dim a As String
a = (Worksheets("Sheet1").Range("A3"))


Set r = lbdata
Dim lb As msforms.listbox


Dim rcArray() As Variant
Dim lrw As Long, lcol As Long
Dim rngTarget As Range

'Define the range you want to use
Set rngTarget = rMyCell




'Set the boundaries of the array
ReDim Preserve rcArray(1 To rngTarget.Rows.Count, 1 To rngTarget.Columns.Count)

'Fill the array with data from the worksheet
With rngTarget
For lcol = 1 To .Columns.Count
For lrw = 1 To .Rows.Count
rcArray(lrw, lcol) = rngTarget.Cells(lrw, lcol)
Next lrw
Next lcol
End With

'Place the array in the combobox
Set lb = lbdata
With lb

.List = rcArray
End With


End Function

Cheers, gang