Listbox Help

[Guest]

I am new to the whole VBA world. But I have figured out how to populate a
list box in a Userform with options, however I can not seam to figure out how
to take the selected option and populate it in one of my sheets to keep track
of the seletions.

Here is what I have

Sub Question1()

Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Array("Apples", "Oranges", "Peaches", "Bananas", "Pineapples")

For Ctr = LBound(MyArray) To UBound(MyArray)
UserForm11.ListBox1.AddItem MyArray(Ctr)
Next
UserForm11.Show

End Sub

If the user selects "Apples", I want "Apples" to populate in cell A1 in
sheet 1
TIA

 

[Dave Peterson]

I put all this behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Dim DestCell As Range
With Worksheets("sheet1")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
With Me.ListBox1
If .ListIndex > -1 Then
DestCell.Value = .Value
End If
End With
End Sub
Private Sub UserForm_Initialize()
Dim MyArray As Variant
MyArray = Array("Apples", "Oranges", "Peaches", "Bananas", "Pineapples")
Me.ListBox1.List = MyArray
End Sub

 

[David G]

The easier way to do this is to provide the ListBox with a
ControlSource cell.
See the additional line below:

Sub Question1()

Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Array("Apples", "Oranges", "Peaches", "Bananas", "Pineapples")

UserForm11.ListBox1.ControlSource = "Sheet1!A1"

For Ctr = LBound(MyArray) To UBound(MyArray)
UserForm11.ListBox1.AddItem MyArray(Ctr)
Next
UserForm11.Show

End Sub

Cheers,
David G