List Box Multiselect Form

[ann_nyc]

Hi -

I am creating a form in Excel and using Controls to collect information
and then feed the values to a another sheet in the same workbook. The end
game here is to pass these values along to another workbook.
Everything is working well - I have text boxes and List boxes displayed as
option
(single select radio buttons) and my data is showing up in the cell that I
have referenced in the LinkCell in the properties dialog. The rub is when I
want to create a Multiselect List. The link cell is ignored when you change
the option to Multiselect. How can I have a number of check boxes that feed
into the single referenced cell?

Many Thanks for your help,
Nancy

 

[Dave Peterson]

If you want the listbox to be able to have multiple selections, you'll have to
save each value--or save the true/false-ness of each of the options.

You can go through each of the options:

Private Sub CommandButton1_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
'do what you want
MsgBox .List(iCtr)
End If
Next iCtr
End With
End Sub

But I'm not sure what you really want to keep track of.

 

[ann_nyc]

HI Dave and thanks for responding. I am asking a user to fill out a form and
return it to me. Their answers will be fed to two places eventutually: a
project specific worksheet and then a master project list. One of the
questions has the user able to select more than one answer. For example:
Q: What letters do you like (select all that apply):
Apples
Melons
Oranges
Grapes
Pears
So if the user selects Melons, Grapes and Pears, I would want the values
Melons, Grapes and Pears to be fed to a single cell.

Does this better explain? Once again, any help would be appreciated.

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim sStr as String
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
sStr = sStr & .List(iCtr) & ", "
End If
Next iCtr
End With
if sStr <> "" then
sStr = Left(sStr,len(sStr)-2)
Range("B9").Value = sStr
End if
End Sub

 

[ann_nyc]

Hi Tom - Brilliant. This got me 90% of the way there. Can you tell me how to
make this work without a CommandButton? The user never actually submits the
form. I just need the values passed along to the cell without any action
other than selecting and deselecting the check box. Is that possible?

 

[Dave Peterson]

Maybe just using the _change event would be sufficient:

Option Explicit
Private Sub ListBox1_Change()
Dim iCtr As Long
Dim sStr As String
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
sStr = sStr & .List(iCtr) & ", "
End If
Next iCtr
End With
If sStr <> "" Then
sStr = Left(sStr, Len(sStr) - 2)
Range("B9").Value = sStr
End If
End Sub

Hi Tom - Brilliant. This got me 90% of the way there. Can you tell me how to
make this work without a CommandButton? The user never actually submits the
form. I just need the values passed along to the cell without any action
other than selecting and deselecting the check box. Is that possible?