vba - how do I put excel column in vba listbox

[dsauvage]

Be patient, I'm new to vba.....

Software: Excel 2000 & ACAD 2002

I have a spreadsheet that has multiple columns, I want the first column to appear as a listbox in a vba form. I have it working provided the user clicks a button to fill the listbox, but I want the lisbox to be populated upon intializing.

This is what I have for the button, and it works flawlessly,

Private Sub CommandButton1_Click()

Call Set_Up_Excel 'connects to excel

'frmMain.Hide 'hide the form

Err.Clear
Steel_Shp.ColumnCount = 1 'set number of columns to show
Steel_Shp.List = Worksheets("Sheet1&quot.Range("A1:A40&quot.Value 'select range to show A1 (column A row1 to column A row 40)

'frmMain.Show 'show the form

End Sub

I tried to intialize in

Private Sub frmMain_Initialize()

But nothing Happens ??

Can someone help?

Thanks

Don Sauvage

 

[JockeK]

You need to set the rowsource of the listbox.

Private Sub frmMain_Initialize()
NameOfYourListbox.RowSource = Worksheets("Sheet1&quot.Range("A1:A40&quot
End Sub

 

[Whisperer]

Don,

although your efforts to use VBA are commendable, there is no need to use it to populate your listbox. As JockeK has already stated, the only thing that you need to do is to tell the listbox the RowSource criteria.

In the VBA editor if you right-click on the Listbox concerned and select Properties, you can see all the properties that can be preset within the listbox, these include column and of course rowsource.

In the Rowsource property you would type:

'Sheet1'!A1:A40

and that is all there is to it. Any setting that is made in the properties window can always be overridden by code if required.

A final suggestion is that if you select a property that you are unsure of, e.g. ColumnWidth, the help using F1 is really very good and you will advance quite quickly in your ability to use VBA and the built in functions that are available.

Hope that helps

Gordon