Help using list boxes

[lcfc2kotmail]

Hi, I have a quick question im familair with coding VBA within excel however I am trying to create a program for my project and require any tips for the following



I have an array of data 30 colums byt say 20 rows to make this simple. Each column represents data from a company and I would like to analyse these.



What I would want to know if there is a way I can have a list box that can have the names of these 30 companies and each of the companies once selected is linked to that row of data. Once the user select the number of companies they want, this data is then read into a multidemntional array say M (r , c) , where r is rows and c is columns.



This will give me a flexible option of the software allowing the user to select companies rather than having to use a fixed array with that data stored in.



e.g.



company 1 company 2 company 3

1 3 4

3 4 6

6 1 3



This data would be in say sheet 1, my program in sheet 2, and would contain a list box: Company 1

Company 2

Company 3

It will allow the user to select multiple options, and for each company selected, it will read in the range of data for that select company into an array. then my program can do the rest based on the data from this array - at the moment it is using a fixed array from all 3 companies.



Many Thanks

[mikerickson]

This routine might do what you want.
Assuming that your data is layed out in 20 rows by 30 columns
, with a company name at the head of each column and the data for that column in the cells below

1)Create a list box from the Forms Menu.
2)Alter the range variable dataRange to suit your situation.
3)Run this routine.
It should fill the list box with the names of the companies.
It also creates a dynamic Named Range called companyData.
This routine requires that the cell to the right of the data range be empty
, it will be used as the Linked Cell for the list box.


Sub loadListOne()
Dim companiesNameRange As Range
Dim dataRange As Range
Dim nameRRay As Variant, xVal As Variant
Dim refersToStr As String

Set dataRange = ThisWorkbook.Sheets("sheet1").Range("a1:ad20"): Rem adjust to suit

Set companiesNameRange = Application.Index(dataRange, 1, 0)
nameRRay = companiesNameRange.Value
With ThisWorkbook.Sheets("sheet1"): Rem sheet with listbox
With .Shapes("List Box 1").OLEFormat.Object
On Error Resume Next
Do
.RemoveItem (1)
Loop Until Err
On Error GoTo 0
For Each xVal In nameRRay
.AddItem xVal
Next xVal
End With
Rem List Box 1 is loaded
With .Shapes("List Box 1").ControlFormat
.LinkedCell = companiesNameRange.Offset(0, companiesNameRange.Columns.Count).Cells(1, 1).Address
refersToStr = "=OFFSET(" & dataRange.Parent.Name & "!" & dataRange.Address: Rem basecell
refersToStr = refersToStr & ",0," & dataRange.Parent.Name & "!" & .LinkedCell & "-1": Rem offset 0 rows, linkedCell columns
refersToStr = referstosr & "," & dataRange.Rows.Count & ",1)": Rem number of rows, single column
ActiveWorkbook.Names.Add Name:="companyData", RefersTo:=refersToStr
End With
End With
End Sub
Once this routine is run, the named range CompanyData refers to the column of cells associated with the name chosen in the list box.

[lcfc2kotmail]

that looks great, I shall give it a go and let you know any feedback.

Many thanks for the prompt reply :)

[lcfc2kotmail]

Hi, I tried this code, Im fairly new to how vba works but generally good at coding.

how would i call this sub procedure, I have

Private Sub ListBox1_Click()

End Sub

then underneath have your code, im wondering as to how this routine is run for the listbox elemnt, im not familiar as you can see and fairly novice at using the form objects.

Many Thanks

[mikerickson]

A control from the forms menu does not event code to be called. The routine should be put in a normal code module, right clicking on the list box will bring up an option to Assign Macro to the list box.