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.

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

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