listbox to show multiple column data

Question

How to get listbox to show data from a set range instead of active sheet?

Private Sub UserForm_INITIALIZE()
 With ListBox1

        .ColumnCount = 2
        .ColumnWidths = "50;50"
        .RowSource = Sheet3.Range("B2:C21").Address
    End With
end sub

Your code work for me --- it populates Listbox1 with the contents of B2:C21. What problem are you having? — xidgel, Jul 01 '21 at 17:27
it is populating info from the active sheet. i need it to populate from sheet3 no matter what sheet is active. — Byron, Jul 01 '21 at 17:30

score 1 · Answer 1 · answered Jul 01 '21 at 17:37

1

How about

Private Sub UserForm_Initialize()
    With ListBox1
        .ColumnCount = 2
        .ColumnWidths = "50;50"
        .RowSource = "Sheet3!B2:C21"
    End With
End Sub

answered Jul 01 '21 at 17:37

xidgel

3,085
2
13
22

The code runs for me. I can't reproduce the error you're getting. – xidgel Jul 01 '21 at 18:05

score 1 · Answer 2 · answered Jul 01 '21 at 18:18

1

You can also use .List instead of .RowSource

Private Sub UserForm_Initialize()
    With ListBox1
        .ColumnCount = 2
        .ColumnWidths = "50;50"
        .List = ThisWorkbook.Worksheets("Sheet3").Range("B2:C21").Value
    End With
End Sub

answered Jul 01 '21 at 18:18

xidgel

3,085
2
13
22

score 1 · Answer 3 · answered Jul 01 '21 at 19:07

What is causing the active sheet reference?

Using the Address property - though explicitly addressing the project's Sheet (Code)Name Sheet3

    .RowSource = Sheet3.Range("B2:C21").Address

will only assign the cells' range address "$B$2:$C$21", not the tabular worksheet reference.

To get the full reference Sheet3!B2:C21 you could either set the External argument to True via

Sheet3.Range("A1:B7").Address(False,False,External:=True)

or prefix the range address string by the worksheet Name plus !

Sheet3.Name & "!" & Tabelle1.Range("A1:B7").Address(False,False)

Further issue

As you mentioned Error 70 ("permission denied"), it's very likely that you have further code trying to add rows to the listbox'es .List property which doesn't get on well together.

Personally I'd do without .RowSource at all and handle the listbox list via array assignments or use an intermediate hidden sheet with rowsource data you can manipulate.

.

score -1 · Accepted Answer · answered Jul 01 '21 at 17:45

-1

 Dim Data As Variant
Dim lastrow As Long
lastrow = Sheet3.Cells(Rows.Count, "b").End(xlUp).Row
ListBox1.ColumnCount = 2
ListBox1.ColumnWidths = "50;50"
Data = Sheet3.Range("b2:c" & lastrow)
ListBox1.List = Data

answered Jul 01 '21 at 17:45

Byron

9
3

4

Code-only answers are not particularly helpful. Please add some descriptions of how this code solves the problem. – Sven Eberth Jul 02 '21 at 22:28

listbox to show multiple column data

4 Answers4