Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I retrieve multiple selections from a forms listbox?
I am creating a worksheet in which the users will select choices from a
multi-select listbox. I have been able to do this using VBA code with a ActiveX listbox from the Controls Toolbar. However, I don't need the complexity (and associated problems) of an ActiveX control, so I would prefer to use a listbox from the Forms Toolbar. Unfortunately, with multi-select, the cell link is inactive, but I have found no instruction on how to link the user's selections to a spreadsheet. Can anyone offer suggestions on how to retrieve the user's selections from a Forms listbox? Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I retrieve multiple selections from a forms listbox?
"Wheeler" wrote in message
... I am creating a worksheet in which the users will select choices from a multi-select listbox. I have been able to do this using VBA code with a ActiveX listbox from the Controls Toolbar. However, I don't need the complexity (and associated problems) of an ActiveX control, so I would prefer to use a listbox from the Forms Toolbar. Unfortunately, with multi-select, the cell link is inactive, but I have found no instruction on how to link the user's selections to a spreadsheet. There's no way to automatically link the results of a multiselect ListBox of either type directly to worksheet cells. You have to loop the whole list and use the Selected property to determine which items are selected. Here's an example: Sub GetSelections() Dim lIndex As Long Dim szSelections As String For lIndex = 1 To Sheet1.ListBoxes(1).ListCount If Sheet1.ListBoxes(1).Selected(lIndex) Then szSelections = szSelections & _ Sheet1.ListBoxes(1).List(lIndex) & vbLf End If Next lIndex If Len(szSelections) 0 Then MsgBox szSelections End Sub -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I retrieve multiple selections from a forms listbox?
Thanks for the response Rob.
I knew I had to loop through, but I was missing something in the code I was trying to use. Using your example, I was able to figure out what I needed. Thanks again, very much! Wheeler "Rob Bovey" wrote: "Wheeler" wrote in message ... I am creating a worksheet in which the users will select choices from a multi-select listbox. I have been able to do this using VBA code with a ActiveX listbox from the Controls Toolbar. However, I don't need the complexity (and associated problems) of an ActiveX control, so I would prefer to use a listbox from the Forms Toolbar. Unfortunately, with multi-select, the cell link is inactive, but I have found no instruction on how to link the user's selections to a spreadsheet. There's no way to automatically link the results of a multiselect ListBox of either type directly to worksheet cells. You have to loop the whole list and use the Selected property to determine which items are selected. Here's an example: Sub GetSelections() Dim lIndex As Long Dim szSelections As String For lIndex = 1 To Sheet1.ListBoxes(1).ListCount If Sheet1.ListBoxes(1).Selected(lIndex) Then szSelections = szSelections & _ Sheet1.ListBoxes(1).List(lIndex) & vbLf End If Next lIndex If Len(szSelections) 0 Then MsgBox szSelections End Sub -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Chart from Userform ListBox selections | Charts and Charting in Excel | |||
Forms with conditions & mandatory selections | Excel Worksheet Functions | |||
Display selections from a listbox in a message box | Excel Programming | |||
Jumping to chart based on listbox selections | Excel Programming | |||
changing foreground color of listbox w/o losing selections | Excel Programming |