« Replace all #N/A: VBA Excel help | Main | Convert current sheets to columns and convert current columns to sheet names: VBA Excel »
Thursday
Sep062007

Populating a list box from spreadsheet: VBA Excel Help

Sub main()
Dim shtList As Worksheet 'sheet containing values to populate
Dim cell, rng As Range
Dim nRow As Integer 'is the last used  row in the data sheet

Set shtList = ThisWorkbook.Worksheets(Sheets.Count)  'sets the last sheet as the sheet value sheet
nRow = shtList.Cells(1, 1).End(xlDown).Row 'sets the last used row

'First create a form, and then create a listbox inside the form. In this example I've named the form "listform" and the listbox "listbox"

With ListForm.ListBox
    .Clear
    .ColumnCount = 2 'how many columns are in the listbox
    .List = Range(Cells(1, 1), Cells(nRow, 2)).Value 'populate the listbox with the values in the range
End With

ListForm.Show

End Sub

Reader Comments (3)

Tnx alot for the info! :)

May 8, 2010 | Unregistered CommenterLucas J

ヅ You're very welcome.

November 19, 2010 | Registered CommenterGleb

Thank you thank you thank you thank you

June 29, 2014 | Unregistered CommenterMe

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
All HTML will be escaped. Textile formatting is allowed.