Dynamic Range from sheet1 in Listbox1 on Userform

[Qmustard]

Hi all,

Looking for what I assume is an easy answer.

I have three columns on sheet1 with data.
They will dynamically change length based on how the data is inputted via other methods.

I’d like to populate my listbox1 on userform1 with this data from all three columns.

I’ve been able to do it with one column, but not with all three.

I’ve been messing with listbox1.rowsource but been unable to get it to populate only what’s in the cells.

Thanks in advance!

 

[MickG]

Make sure the "RowSource" property is blank.

Then place this code in your userform module.

Private Sub UserForm_Initialize()
With Me.ListBox1
    .ColumnCount = 3
    .List = Sheets("Sheet1").Cells(1).CurrentRegion.Resize(, 3).Value
End With
End Sub

 

[Qmustard]

Make sure the "RowSource" property is blank.

Then place this code in your userform module.

Private Sub UserForm_Initialize()
With Me.ListBox1
    .ColumnCount = 3
    .List = Sheets("Sheet1").Cells(1).CurrentRegion.Resize(, 3).Value
End With
End Sub

Ah! Thank you MickG! I knew it would be something simple! Thank you!


What I forgot to ask, was is it possible to remove the first cells? So it starts from A2 etc..?

 

[MickG]

Try this:-

Private Sub UserForm_Initialize()
Dim Rng As Range
Set Rng = Cells(1).CurrentRegion.Resize(, 3)
With Me.ListBox1
    .ColumnCount = 3
    .List = Rng.Offset(1).Resize(Rng.Rows.Count - 1, 3).Value
End With
End Sub