Listbox source

[mrmrf]

Hi.

I am having trouble showing source data in a listbox.

I use the below code to enter data from the userform to a worksheet, then the last two lines of code displays all data within a listbox. And this works ok.

But on first open of the userform i want the listbox to show the data already entered without needing to add more and using the code below.

I have edited the rowsource of the listbox to be 'ASamples'!A1:L6535 but this only shows data in the listbox from column 0, and doesnt show the other columns.

Any tips?

Thanks.

Private Sub CommandButtonAddSample_Click()
Dim wks As Worksheet
Dim AddNew As Range
Set wks = Sheets("ASamples")
Set AddNew = wks.Range("A65356").End(xlUp).Offset(1, 0)
AddNew.Offset(0, 0).Value = txtA1.Text
AddNew.Offset(0, 1).Value = txtA2.Text
AddNew.Offset(0, 2).Value = txtA3.Text
AddNew.Offset(0, 3).Value = txtA4.Text
AddNew.Offset(0, 4).Value = txtA5.Text
AddNew.Offset(0, 9).Value = txtA6.Text
AddNew.Offset(0, 10).Value = txtA7.Text

lstDisplay.ColumnCount = 12
lstDisplay.RowSource = "'ASamples'!A1:L65356"
End Sub

<strike></strike>

 

[Yongle]

But on first open of the userform i want the listbox to show the data already entered without needing to add more and using the code below.

Using code ...

Private Sub UserForm_Initialize()
    lstDisplay.ColumnCount = [B]12[/B]

[COLOR=#006400]'avoid putting so many rows into the listbox[/COLOR]
    With Sheets("ASamples")
[COLOR=#006400]        'if a contiguous range[/COLOR]
        lstDisplay.RowSource = .Range("A1").CurrentRegion.Resize(, 12).Address
[COLOR=#006400]        'OR assuming column A contains a value in every row[/COLOR]
        lstDisplay.RowSource = .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 12).Address
    End With
End Sub

Use the Properties window ...
- amend the listbox Column Count property to 12

 

[mrmrf]

So simple! Thank you!

 

[Yongle]

It nearly always is !
thanks for the feedback