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!

*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

how to initialize a listbox in excel

how to initialize a listbox in excel

how to initialize a listbox in excel

If only someone could give me a start. I want to use combos or listboxes in my spreadsheets. I know how to code the events attached to this kind of controls. What I havent yet been able to figure out(I need too much time for this) is how can I for example fill the box initially with bolt grades for user to choose from.

Thanks in advance.

RE: how to initialize a listbox in excel

You would first place your bolt grade "choices" into a series of vertically stacked cells (i.e. A1 through A3). Usually off the spreadsheet's normal viewing area.

Open the Forms toolbar and select listbox.  Your curser is now a black cross.  Drag on the spreadsheet the size of listbox you want.  Right click on the listbox and select Format Control.  

Select the Control tab on the Format Control window.  The Input Range would then be entered as A1:A3.  The Cell Link is the cell where you want your selected choice to be indicated.  Perhaps you enter A4 for the cell link.  Now, when you pick on one of the items in the box, Cell A4 reports the number of the item you select.

Hope this is what you were looking for.

RE: how to initialize a listbox in excel

thanks JAE. It is really great to have you around. See you in Structural Forumns too.

RE: how to initialize a listbox in excel

While your methods work for a fixed size list, you would have to redefine the listbox if you want to add more items to the list. You should use the Form_Activate event to fill in the listbox.

Assume that your list is on a sheet called DataSheet. The first row (A1) contains the heading for the list and the first data item is on row 2 (A2).

Add this code to your form. Then, if you need to add to your list, just fill in the next empty cell in that row. Next time the program is run, you will have an updated list.

Private Sub UserForm_Activate()
    Dim sVal As String
    Dim dataSheet As Worksheet
    Dim iDataRow As Long
    Set dataSheet = Sheets("DataSheet")
    'Fill in the listbox
    'Column A contains the item list (Row 1 is the column header)
    iDataRow = 2    'first item in the list
    Do While Len(dataSheet.Range("A" & iDataRow).Text) > 0
        sVal = dataSheet.Range("A" & iDataRow).Text
        ListBox1.AddItem sVal
        iDataRow = iDataRow + 1
End Sub

This will enable other users to simply add data with no knowledge of programming.

Hope this helps!

RE: how to initialize a listbox in excel

Oops! It looks like I missed the fact that you are using the listbox on the worksheet and not on a form. You can add/tweak this code for your sheet:

Option Explicit
Option Base 1

'Module level array
Dim sList()

Private Sub Worksheet_Activate()
    Dim i As Long
    Call GetDataList
    For i = LBound(sList) To UBound(sList)
        ListBox1.AddItem sList(i)
    Next i
    ReDim sList(1)
End Sub

Private Sub GetDataList()
    Dim iItems As Long
    Dim iDataRow As Long
    Dim dataSheet As Worksheet
    Set dataSheet = Sheets("DataSheet")
    ReDim sList(1)
    iDataRow = 2    'first item in the list
    iItems = 0
    Do While Len(dataSheet.Range("A" & iDataRow).Text) > 0
        iItems = iItems + 1
        ReDim Preserve sList(iItems)
        sList(iItems) = dataSheet.Range("A" & iDataRow).Text
        iDataRow = iDataRow + 1
    Set dataSheet = Nothing
End Sub

RE: how to initialize a listbox in excel

Keep yourself in the forum among Ron and JAE. Thanks and all the best. Simply said, you are saving us big chunks of time.

RE: how to initialize a listbox in excel

No problem! Glad I could be of assistance. I have applied my engineering skills towards the development of specialty engineering software. I have developed everything from engineering spreadsheets to the automation of CAD software. It is amazing how little of the capabilities are used in the industry.

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! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close