×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

vba - how do I put excel column in vba listbox

vba - how do I put excel column in vba listbox

vba - how do I put excel column in vba listbox

(OP)
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").Range("A1:A40").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

RE: vba - how do I put excel column in vba listbox

You need to set the rowsource of the listbox.

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

RE: vba - how do I put excel column in vba listbox

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources