PDA

View Full Version : Query for Help



lcfc2kotmail
11-27-2007, 01:48 PM
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 A1:D1

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
11-27-2007, 04:07 PM
Dim myArray as Variant
myArray = Selection.Range("A2:A19").Value

lcfc2kotmail
11-27-2007, 04:27 PM
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
11-27-2007, 05:59 PM
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
12-02-2007, 06:32 PM
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..