Query for Help

[lcfc2kotmail]

Hi does anybody have any infromation how I can have a list box selection related to a range.
e.g. the names I want to appear in a list box are in columns A11

and for each select say A1, I want the range of A2:A19 to be read into an array, and this done for any selection, it can be multiple selections.

Many Thanks

[mikerickson]

Dim myArray as Variant
myArray = Selection.Range("A2:A19").Value

[lcfc2kotmail]

Hi, how does tha help me with using it within A list box in a user form?

A1-D1 need to be in rows and those names will be in my list box

if A1 is selected, I need A2-A19 to be read into a 2D array or if B1 selected, I need B2-B19 etc....

so if 2 of these are selected Ill had a 2 x 20 array, if 3, ill have a 3 x 20 array etc..

Many Thanks

[mikerickson]

If you declare a public Variable in a normal module.

Public dataRRay as Variant

This code in the userform's code module will fill it with the values, based on what you select from the list box.

Private Sub ListBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Integer, rowNum As Long
Dim selectedCount As Integer
ReDim dataRRay(1 To 20, 1 To 5)

With Me.ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            selectedCount = selectedCount + 1
            For rowNum = 1 To 20
                dataRRay(rowNum, selectedCount) = ThisWorkbook.Sheets(1).Cells(rowNum, selectedCount)
            Next rowNum
        End If
    Next i
End With
If selectedCount > 0 Then ReDim Preserve dataRRay(1 To 20, 1 To selectedCount)
End Sub


Private Sub UserForm_Initialize()
Dim i As Integer
With Me.ListBox1
    For i = 1 To 4
        .AddItem ThisWorkbook.Sheets(1).Cells(1, i).Value
    Next i
End With
End Sub

If no items are selected from the list box, then Ubound(dataRRay,2) = 5,
otherwise UBound(dataRRay,2) = the number of items chosen from the list box.

[lcfc2kotmail]

Hi Many Thanks for this,

I have entered this code and when I run my code I get the user form up with values A-D as needed, however how can I run this so that when the ones I selected are higlighted, I press a command button that says these are your selections confirm.

This would help alot many thanks.

So far from the code given it shows the list box with values but cant do anything..