List Box Multiselect Form
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?
"Tom Ogilvy" wrote:
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
--
Regards,
Tom Ogilvy
"ann_nyc" wrote in message
...
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.
"Dave Peterson" wrote:
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 wrote:
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
|