PDA

View Full Version : VB code for cell linked to multiple selection list box



HeatherA
07-16-2009, 12:44 PM
I have a list box that is multiple selection enabled. My linked cell returns N/A. I need VB code (or another way) for that cell to process the multiple selections.

Also, how would the multiple selections appear in the linked cell? For example, if I have 3 items in the list selected, would they be separated by commas in teh linked cell?

Thanks for your help!
Heather

Bob Phillips
07-16-2009, 02:54 PM
I have never used a forms listbox with multi-select but it does appear that once you make it multi-select, the linked cell shows nothing meaningful. Getting the values selected entails VBA.

Assign this macro to the listbox



Public Function myList_Change()
Dim msg As String
Dim i As Long
Dim appCaller As String

With ActiveSheet

appCaller = Application.Caller
msg = ""
On Error GoTo end_loop
Do While i < 100
i = i + 1
'.ListBoxes (appCaller)
If .ListBoxes(appCaller).Selected(i) Then

msg = msg & .ListBoxes(appCaller).List(i) & vbNewLine
End If
Loop

end_loop:
On Error GoTo 0
MsgBox msg
End With
End Function