Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nice easy Listbox queries
Good afternoon, all!
I'm trying to take the selected items from the listbox and add them to the worksheet range "Target" However, I'm not quite sure how to reference the value of the selected item(s) from the listbox. Any helpful souls out there? Thanks in advance Pete Private Sub UserForm_Initialize() Range("Target").ClearContents Dim ListCell As Range For Each ListCell In Range("CarList") ListBox1.AddItem ListCell.Value Next End Sub Private Sub cmdOK_Click() Dim I As Integer For I = 1 To ListBox1.ListCount If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1(I).Text <--------- ! End If Next I Unload Me End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nice easy Listbox queries
Peter,
VBA already does it for you Listbox1.Value no need to iterate through the list. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to take the selected items from the listbox and add them to the worksheet range "Target" However, I'm not quite sure how to reference the value of the selected item(s) from the listbox. Any helpful souls out there? Thanks in advance Pete Private Sub UserForm_Initialize() Range("Target").ClearContents Dim ListCell As Range For Each ListCell In Range("CarList") ListBox1.AddItem ListCell.Value Next End Sub Private Sub cmdOK_Click() Dim I As Integer For I = 1 To ListBox1.ListCount If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1(I).Text <--------- ! End If Next I Unload Me End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nice easy Listbox queries
Sorry, Bob, you lost me.
How does this work for multiple selections, then? Private Sub cmdOK_Click() Dim I As Integer For I = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1.Value End If Next I Unload Me End Sub doesn't put anything in my worksheet range at all, either for single or multiple selections. I use the loop to populate the cells in the target range, although I admit I could do it another way, using OFFSET. Sorry to be dense. Pete "Bob Phillips" wrote: Peter, VBA already does it for you Listbox1.Value no need to iterate through the list. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to take the selected items from the listbox and add them to the worksheet range "Target" However, I'm not quite sure how to reference the value of the selected item(s) from the listbox. Any helpful souls out there? Thanks in advance Pete Private Sub UserForm_Initialize() Range("Target").ClearContents Dim ListCell As Range For Each ListCell In Range("CarList") ListBox1.AddItem ListCell.Value Next End Sub Private Sub cmdOK_Click() Dim I As Integer For I = 1 To ListBox1.ListCount If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1(I).Text <--------- ! End If Next I Unload Me End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nice easy Listbox queries
Try the following
Private Sub UserForm_Initialize() Range("Target").ClearContents Dim ListCell As Range For Each ListCell In Range("CarList") ListBox1.AddItem ListCell.Value Next End Sub Private Sub cmdOK_Click() Dim I As Integer For I = 1 To ListBox1.ListCount If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1.List(I) End If Next I Unload Me End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nice easy Listbox queries
It wouldn't! <g
Dim I As Integer, j As Integer For I = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(I) = True Then j = j + 1 Range("Target").Cells(j, 1).Formula = ListBox1.List(I) End If Next I Unload Me -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peter Rooney" wrote in message ... Sorry, Bob, you lost me. How does this work for multiple selections, then? Private Sub cmdOK_Click() Dim I As Integer For I = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1.Value End If Next I Unload Me End Sub doesn't put anything in my worksheet range at all, either for single or multiple selections. I use the loop to populate the cells in the target range, although I admit I could do it another way, using OFFSET. Sorry to be dense. Pete "Bob Phillips" wrote: Peter, VBA already does it for you Listbox1.Value no need to iterate through the list. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to take the selected items from the listbox and add them to the worksheet range "Target" However, I'm not quite sure how to reference the value of the selected item(s) from the listbox. Any helpful souls out there? Thanks in advance Pete Private Sub UserForm_Initialize() Range("Target").ClearContents Dim ListCell As Range For Each ListCell In Range("CarList") ListBox1.AddItem ListCell.Value Next End Sub Private Sub cmdOK_Click() Dim I As Integer For I = 1 To ListBox1.ListCount If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1(I).Text <--------- ! End If Next I Unload Me End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nice easy Listbox queries
Bob,
That cracked it! Thanks very much! Have a green tick and a good weekend! :-) Pete "Bob Phillips" wrote: It wouldn't! <g Dim I As Integer, j As Integer For I = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(I) = True Then j = j + 1 Range("Target").Cells(j, 1).Formula = ListBox1.List(I) End If Next I Unload Me -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peter Rooney" wrote in message ... Sorry, Bob, you lost me. How does this work for multiple selections, then? Private Sub cmdOK_Click() Dim I As Integer For I = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1.Value End If Next I Unload Me End Sub doesn't put anything in my worksheet range at all, either for single or multiple selections. I use the loop to populate the cells in the target range, although I admit I could do it another way, using OFFSET. Sorry to be dense. Pete "Bob Phillips" wrote: Peter, VBA already does it for you Listbox1.Value no need to iterate through the list. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to take the selected items from the listbox and add them to the worksheet range "Target" However, I'm not quite sure how to reference the value of the selected item(s) from the listbox. Any helpful souls out there? Thanks in advance Pete Private Sub UserForm_Initialize() Range("Target").ClearContents Dim ListCell As Range For Each ListCell In Range("CarList") ListBox1.AddItem ListCell.Value Next End Sub Private Sub cmdOK_Click() Dim I As Integer For I = 1 To ListBox1.ListCount If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1(I).Text <--------- ! End If Next I Unload Me End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nice easy Listbox queries
Matt,
Thanks for your help. However, I think you need to set up a seperate variable to determine where in the "Target" range the selected items are to appear. If you use I for this, the first item selected from the listbox appears one cell above the first cell of "Target". Bob supplied the following, incorporating an additional "positioning" variable: Private Sub cmdOK_Click() Dim I As Integer, J As Integer For I = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(I) = True Then J = J + 1 Range("Target").Cells(I, 1).Formula = ListBox1.List(I) End If Next I Unload Me End Sub Thanks again, and have a good weekend! :-) Pete Private Sub UserForm_Initialize() Range("Target").ClearContents Dim ListCell As Range For Each ListCell In Range("CarList") ListBox1.AddItem ListCell.Value Next End Sub "Matt" wrote: Try the following Private Sub UserForm_Initialize() Range("Target").ClearContents Dim ListCell As Range For Each ListCell In Range("CarList") ListBox1.AddItem ListCell.Value Next End Sub Private Sub cmdOK_Click() Dim I As Integer For I = 1 To ListBox1.ListCount If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1.List(I) End If Next I Unload Me End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nice easy Listbox queries
Thanks, I will.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peter Rooney" wrote in message ... Bob, That cracked it! Thanks very much! Have a green tick and a good weekend! :-) Pete "Bob Phillips" wrote: It wouldn't! <g Dim I As Integer, j As Integer For I = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(I) = True Then j = j + 1 Range("Target").Cells(j, 1).Formula = ListBox1.List(I) End If Next I Unload Me -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peter Rooney" wrote in message ... Sorry, Bob, you lost me. How does this work for multiple selections, then? Private Sub cmdOK_Click() Dim I As Integer For I = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1.Value End If Next I Unload Me End Sub doesn't put anything in my worksheet range at all, either for single or multiple selections. I use the loop to populate the cells in the target range, although I admit I could do it another way, using OFFSET. Sorry to be dense. Pete "Bob Phillips" wrote: Peter, VBA already does it for you Listbox1.Value no need to iterate through the list. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to take the selected items from the listbox and add them to the worksheet range "Target" However, I'm not quite sure how to reference the value of the selected item(s) from the listbox. Any helpful souls out there? Thanks in advance Pete Private Sub UserForm_Initialize() Range("Target").ClearContents Dim ListCell As Range For Each ListCell In Range("CarList") ListBox1.AddItem ListCell.Value Next End Sub Private Sub cmdOK_Click() Dim I As Integer For I = 1 To ListBox1.ListCount If ListBox1.Selected(I) = True Then Range("Target").Cells(I, 1).Formula = ListBox1(I).Text <--------- ! End If Next I Unload Me End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
very nice........... | Excel Worksheet Functions | |||
Thank you for a nice party | Excel Discussion (Misc queries) | |||
Nice | Excel Discussion (Misc queries) | |||
Easy Listbox to Sheet? | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming |