Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Selection List/Combi Box
Hi
How can I set up a list/combi box, which allows the selection of more than 1 of the listed items, using for examaple the ctrl key to select multi items. Thanks in advance for your help Richard |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Selection List/Combi Box
You can't use a combobox for this.
But you can use a listbox from the control toolbox toolbar. Rightclick on the combobox and choose properties (make sure you're in design mode first). Then you can look for the MultiSelect property and change it to fmmultiselectmulti. (Same technique if you're using a listbox on a userform.) === If you're using a listbox from the Forms toobar, you can rightclick on it and choose "Format Control". On the Control tab, change the Selection type to Multi. Richard wrote: Hi How can I set up a list/combi box, which allows the selection of more than 1 of the listed items, using for examaple the ctrl key to select multi items. Thanks in advance for your help Richard -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Selection List/Combi Box
Dave,
I am using a listbox on the userform and have changed the property to MultSelect. It works just how I would like it to work, however how do I retrieve the text of those selections? For example, the list is populated by text values in a column such as: name 1 name 2 name 3 name 4 In the listbox for example if I select name 1 and name 3, I would like those text values to populate a column in another sheet. I would like to know, what is the syntax for retrieving those values. I've tried .List but it only gives me the first text value (i.e. name 1). Also, ..Value doesn't seem to give me anything either. Help would be much appreciated. Thank you. "Dave Peterson" wrote: You can't use a combobox for this. But you can use a listbox from the control toolbox toolbar. Rightclick on the combobox and choose properties (make sure you're in design mode first). Then you can look for the MultiSelect property and change it to fmmultiselectmulti. (Same technique if you're using a listbox on a userform.) === If you're using a listbox from the Forms toobar, you can rightclick on it and choose "Format Control". On the Control tab, change the Selection type to Multi. Richard wrote: Hi How can I set up a list/combi box, which allows the selection of more than 1 of the listed items, using for examaple the ctrl key to select multi items. Thanks in advance for your help Richard -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Selection List/Combi Box
I created a small userform with a commandbutton and a listbox on it. Maybe
this'll give you an idea. Option Explicit Private Sub CommandButton1_Click() Dim iCtr As Long Dim DestCell As Range With Worksheets("Sheet1") Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With With Me.ListBox1 For iCtr = 0 To .ListCount - 1 If .Selected(iCtr) = True Then DestCell.Value = .List(iCtr) Set DestCell = DestCell.Offset(1, 0) End If Next iCtr End With End Sub Private Sub UserForm_Initialize() Dim iCtr As Long Me.CommandButton1.Caption = "Ok" 'you can also specify that setting in code Me.ListBox1.MultiSelect = fmMultiSelectMulti For iCtr = 1 To 5 Me.ListBox1.AddItem "Name " & iCtr Next iCtr End Sub HF wrote: Dave, I am using a listbox on the userform and have changed the property to MultSelect. It works just how I would like it to work, however how do I retrieve the text of those selections? For example, the list is populated by text values in a column such as: name 1 name 2 name 3 name 4 In the listbox for example if I select name 1 and name 3, I would like those text values to populate a column in another sheet. I would like to know, what is the syntax for retrieving those values. I've tried .List but it only gives me the first text value (i.e. name 1). Also, .Value doesn't seem to give me anything either. Help would be much appreciated. Thank you. "Dave Peterson" wrote: You can't use a combobox for this. But you can use a listbox from the control toolbox toolbar. Rightclick on the combobox and choose properties (make sure you're in design mode first). Then you can look for the MultiSelect property and change it to fmmultiselectmulti. (Same technique if you're using a listbox on a userform.) === If you're using a listbox from the Forms toobar, you can rightclick on it and choose "Format Control". On the Control tab, change the Selection type to Multi. Richard wrote: Hi How can I set up a list/combi box, which allows the selection of more than 1 of the listed items, using for examaple the ctrl key to select multi items. Thanks in advance for your help Richard -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Selection List/Combi Box
So when changing the property to allow multi selection, does the Linked cell
become inactive, so that the only way to return the multi items selected is by using code. "Dave Peterson" wrote: I created a small userform with a commandbutton and a listbox on it. Maybe this'll give you an idea. Option Explicit Private Sub CommandButton1_Click() Dim iCtr As Long Dim DestCell As Range With Worksheets("Sheet1") Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With With Me.ListBox1 For iCtr = 0 To .ListCount - 1 If .Selected(iCtr) = True Then DestCell.Value = .List(iCtr) Set DestCell = DestCell.Offset(1, 0) End If Next iCtr End With End Sub Private Sub UserForm_Initialize() Dim iCtr As Long Me.CommandButton1.Caption = "Ok" 'you can also specify that setting in code Me.ListBox1.MultiSelect = fmMultiSelectMulti For iCtr = 1 To 5 Me.ListBox1.AddItem "Name " & iCtr Next iCtr End Sub HF wrote: Dave, I am using a listbox on the userform and have changed the property to MultSelect. It works just how I would like it to work, however how do I retrieve the text of those selections? For example, the list is populated by text values in a column such as: name 1 name 2 name 3 name 4 In the listbox for example if I select name 1 and name 3, I would like those text values to populate a column in another sheet. I would like to know, what is the syntax for retrieving those values. I've tried .List but it only gives me the first text value (i.e. name 1). Also, .Value doesn't seem to give me anything either. Help would be much appreciated. Thank you. "Dave Peterson" wrote: You can't use a combobox for this. But you can use a listbox from the control toolbox toolbar. Rightclick on the combobox and choose properties (make sure you're in design mode first). Then you can look for the MultiSelect property and change it to fmmultiselectmulti. (Same technique if you're using a listbox on a userform.) === If you're using a listbox from the Forms toobar, you can rightclick on it and choose "Format Control". On the Control tab, change the Selection type to Multi. Richard wrote: Hi How can I set up a list/combi box, which allows the selection of more than 1 of the listed items, using for examaple the ctrl key to select multi items. Thanks in advance for your help Richard -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Selection List/Combi Box
Yep.
Richard wrote: So when changing the property to allow multi selection, does the Linked cell become inactive, so that the only way to return the multi items selected is by using code. "Dave Peterson" wrote: I created a small userform with a commandbutton and a listbox on it. Maybe this'll give you an idea. Option Explicit Private Sub CommandButton1_Click() Dim iCtr As Long Dim DestCell As Range With Worksheets("Sheet1") Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With With Me.ListBox1 For iCtr = 0 To .ListCount - 1 If .Selected(iCtr) = True Then DestCell.Value = .List(iCtr) Set DestCell = DestCell.Offset(1, 0) End If Next iCtr End With End Sub Private Sub UserForm_Initialize() Dim iCtr As Long Me.CommandButton1.Caption = "Ok" 'you can also specify that setting in code Me.ListBox1.MultiSelect = fmMultiSelectMulti For iCtr = 1 To 5 Me.ListBox1.AddItem "Name " & iCtr Next iCtr End Sub HF wrote: Dave, I am using a listbox on the userform and have changed the property to MultSelect. It works just how I would like it to work, however how do I retrieve the text of those selections? For example, the list is populated by text values in a column such as: name 1 name 2 name 3 name 4 In the listbox for example if I select name 1 and name 3, I would like those text values to populate a column in another sheet. I would like to know, what is the syntax for retrieving those values. I've tried .List but it only gives me the first text value (i.e. name 1). Also, .Value doesn't seem to give me anything either. Help would be much appreciated. Thank you. "Dave Peterson" wrote: You can't use a combobox for this. But you can use a listbox from the control toolbox toolbar. Rightclick on the combobox and choose properties (make sure you're in design mode first). Then you can look for the MultiSelect property and change it to fmmultiselectmulti. (Same technique if you're using a listbox on a userform.) === If you're using a listbox from the Forms toobar, you can rightclick on it and choose "Format Control". On the Control tab, change the Selection type to Multi. Richard wrote: Hi How can I set up a list/combi box, which allows the selection of more than 1 of the listed items, using for examaple the ctrl key to select multi items. Thanks in advance for your help Richard -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Selection List/Combi Box
Thanks Dave, there was syntax that was in your code that I was looking for
and it works! "Dave Peterson" wrote: I created a small userform with a commandbutton and a listbox on it. Maybe this'll give you an idea. Option Explicit Private Sub CommandButton1_Click() Dim iCtr As Long Dim DestCell As Range With Worksheets("Sheet1") Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With With Me.ListBox1 For iCtr = 0 To .ListCount - 1 If .Selected(iCtr) = True Then DestCell.Value = .List(iCtr) Set DestCell = DestCell.Offset(1, 0) End If Next iCtr End With End Sub Private Sub UserForm_Initialize() Dim iCtr As Long Me.CommandButton1.Caption = "Ok" 'you can also specify that setting in code Me.ListBox1.MultiSelect = fmMultiSelectMulti For iCtr = 1 To 5 Me.ListBox1.AddItem "Name " & iCtr Next iCtr End Sub HF wrote: Dave, I am using a listbox on the userform and have changed the property to MultSelect. It works just how I would like it to work, however how do I retrieve the text of those selections? For example, the list is populated by text values in a column such as: name 1 name 2 name 3 name 4 In the listbox for example if I select name 1 and name 3, I would like those text values to populate a column in another sheet. I would like to know, what is the syntax for retrieving those values. I've tried .List but it only gives me the first text value (i.e. name 1). Also, .Value doesn't seem to give me anything either. Help would be much appreciated. Thank you. "Dave Peterson" wrote: You can't use a combobox for this. But you can use a listbox from the control toolbox toolbar. Rightclick on the combobox and choose properties (make sure you're in design mode first). Then you can look for the MultiSelect property and change it to fmmultiselectmulti. (Same technique if you're using a listbox on a userform.) === If you're using a listbox from the Forms toobar, you can rightclick on it and choose "Format Control". On the Control tab, change the Selection type to Multi. Richard wrote: Hi How can I set up a list/combi box, which allows the selection of more than 1 of the listed items, using for examaple the ctrl key to select multi items. Thanks in advance for your help Richard -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Selection List/Combi Box
Thanks for your help, Top Marks
"Dave Peterson" wrote: Yep. Richard wrote: So when changing the property to allow multi selection, does the Linked cell become inactive, so that the only way to return the multi items selected is by using code. "Dave Peterson" wrote: I created a small userform with a commandbutton and a listbox on it. Maybe this'll give you an idea. Option Explicit Private Sub CommandButton1_Click() Dim iCtr As Long Dim DestCell As Range With Worksheets("Sheet1") Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With With Me.ListBox1 For iCtr = 0 To .ListCount - 1 If .Selected(iCtr) = True Then DestCell.Value = .List(iCtr) Set DestCell = DestCell.Offset(1, 0) End If Next iCtr End With End Sub Private Sub UserForm_Initialize() Dim iCtr As Long Me.CommandButton1.Caption = "Ok" 'you can also specify that setting in code Me.ListBox1.MultiSelect = fmMultiSelectMulti For iCtr = 1 To 5 Me.ListBox1.AddItem "Name " & iCtr Next iCtr End Sub HF wrote: Dave, I am using a listbox on the userform and have changed the property to MultSelect. It works just how I would like it to work, however how do I retrieve the text of those selections? For example, the list is populated by text values in a column such as: name 1 name 2 name 3 name 4 In the listbox for example if I select name 1 and name 3, I would like those text values to populate a column in another sheet. I would like to know, what is the syntax for retrieving those values. I've tried .List but it only gives me the first text value (i.e. name 1). Also, .Value doesn't seem to give me anything either. Help would be much appreciated. Thank you. "Dave Peterson" wrote: You can't use a combobox for this. But you can use a listbox from the control toolbox toolbar. Rightclick on the combobox and choose properties (make sure you're in design mode first). Then you can look for the MultiSelect property and change it to fmmultiselectmulti. (Same technique if you're using a listbox on a userform.) === If you're using a listbox from the Forms toobar, you can rightclick on it and choose "Format Control". On the Control tab, change the Selection type to Multi. Richard wrote: Hi How can I set up a list/combi box, which allows the selection of more than 1 of the listed items, using for examaple the ctrl key to select multi items. Thanks in advance for your help Richard -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External data in combi field | Excel Discussion (Misc queries) | |||
Preventing Multi Cell Selection | Excel Discussion (Misc queries) | |||
validation list--list depends on the selection of first list | New Users to Excel | |||
Multi-selection problem in Excel XP and 2003 | Excel Discussion (Misc queries) | |||
limit cell list selection based on the selection of another list | Excel Worksheet Functions |