Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox Help
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 -- Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox Help
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 Pete wrote: 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 -- Pete -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox Help
The easier way to do this is to provide the ListBox with a
ControlSource cell. See the additional line below: On Apr 16, 12:32 pm, Pete wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
avoiding duplicates in listbox (added from another listbox) | Excel Programming | |||
VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |