List Box Multiselect Form
 

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

List Box Multiselect Form
 

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

List Box Multiselect Form
 

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

List Box Multiselect Form
 

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

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

List Box Multiselect Form
 

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



ann_nyc wrote:

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





--

Dave Peterson