Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can I have a multiselect option for a list box in excel 2003 | Excel Discussion (Misc queries) | |||
How do I create a multiselect drop down list | Excel Worksheet Functions | |||
Coding with pivots and Multiselect list | Excel Programming | |||
List Box - MultiSelect | Excel Programming | |||
Multiselect list box | Excel Programming |