How to populate a list box in vba?

Question

I wrote this code to place information from a sheet into a list box in my user form. This is the code I wrote but it is telling me something is out of range although I can't figure out what is out of range.

Private Sub PopulateSearchBox()

Dim wsTL2 As Worksheet
Set wsTL2 = Worksheets("Task List2")
Dim last_row As Long
last_row = wsTL2.Cells(wsTL2.Rows.Count, "C").End(xlUp).Row 'the last populated row in C so it covers the whole range of data that I need.

With Me.searchBox
    .ColumnHeads = True
    .ColumnCount = 3
    .ColumnWidths = "100,100,100"
    .RowSource = "Task List2!A1:C" & last_row
End With

End Sub

https://stackoverflow.com/questions/66867605/how-to-create-a-listbox-with-dynamic-rowsource-in-vba — Siddharth Rout, Nov 11 '22 at 06:45
Try `.RowSource = "'" & wsTL2.Name & "'!" & wsTL2.Range(wsTL2.Cells(1, 1), wsTL2.Cells(last_row, 3)).Address` — Siddharth Rout, Nov 11 '22 at 06:51
It just does not populate the list box but it does not give me an error — David Rojas, Nov 11 '22 at 07:15
How are you calling `PopulateSearchBox`? I do not see anyting which is calling that procedure? :) — Siddharth Rout, Nov 11 '22 at 07:17
Oh I am putting it in the initialize procedure for the userform so it should work as soon as i hit run — David Rojas, Nov 11 '22 at 07:26
I checked your code. It is not there in the `UserForm_Initialize` ;) — Siddharth Rout, Nov 11 '22 at 07:28

score 0 · Answer 1 · answered Nov 11 '22 at 04:13

0

the RowSource property of ComboBox expects an address string. so you could do:

RowSource = wsTL2.Name & "!" & rng.Address
With Me.searchBox
    .ColumnHeads = True
    .ColumnCount = 3
    .ColumnWidths = "100,100,100"
    .RowSource = RowSource
End With

Though it is likely you would be more after the .List property?

Set rng = wsTL2.Cells(wsTL2.Rows.Count, "C").End(xlUp)
With Me.searchBox
    .ColumnHeads = True
    .ColumnCount = 3
    .ColumnWidths = "100,100,100"
    '.RowSource = RowSource
    .List = rng.Value2
End With

answered Nov 11 '22 at 04:13

mtholen

1,631
2
15
27

Well now it is not giving me an error but it still isn't populating the list box. – David Rojas Nov 11 '22 at 04:30
I can see that you have asked this -and similar- questions also in other questions on SO. Perhaps you can describe your entire problem, so people can assist you with a complete solution rather than providing these snippets? The code above a was tested with a simple setup. As I do not have your base data I would not be able to identify what could be wrong? – mtholen Nov 11 '22 at 04:33
Here would you mind if I send you my excel file? I can also attach in the email an explanation of what I want to accomplish in a more general sense. Or would you recommend I just post the whole goal in a question? I can do either. – David Rojas Nov 11 '22 at 04:41
Maybe you can share a dropbox link or so? – mtholen Nov 11 '22 at 04:46
Yeah for sure let me just creat a dropbox and I'll do that and then I can explain what it is I want to accomplish. – David Rojas Nov 11 '22 at 04:51
Hi here is the link https://www.dropbox.com/scl/fi/syqsm5hxwzohba2aqzwbd/Master-Sanitation-Schedules.daily-monthly-version-1.xlsm?dl=0&rlkey=cs5avguqznvym45485dvpzuv1 – David Rojas Nov 11 '22 at 04:56
Ok, got the sheet, so what is the intention... – mtholen Nov 11 '22 at 05:11
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/249502/discussion-between-mtholen-and-david-rojas). – mtholen Nov 11 '22 at 05:13

How to populate a list box in vba?

1 Answers1