how to initialize a listbox in excel 8

[IJR]

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.

 

[JAE]

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.

 

[IJR]

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

 

[dsi]

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
    ListBox1.Clear
    '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
    Loop
End Sub

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

Hope this helps!

 

[dsi]

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
    ListBox1.Clear
    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
    Loop
    Set dataSheet = Nothing
End Sub

 

[IJR]

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

 

[dsi]

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.