Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create multi choice selec drop down list in Excel please
I urgently need to create a drop down list in excel with multiple choice
selections - is this possible please? How do I do it . Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create multi choice selec drop down list in Excel please
In a column to the right of your data, enter the selections you want to use
in the drop down box, one per cell. Move to the cell where you want to start the drop down list and click DATA in the menu and select VALILDATION. Click the combobox drop down to display a list of allowed types and select LIST. Then in the SOURCE field, click the EXPAND dialog box on the right side of the text box, selet the range that has your list and click the COLLAPSE dialog box on the right side of the text box to return to the DATA VALIDATION dialog box. Click OK to complete and then copy the cell with the assigned data validation settings and paste in all the cells you want to use the list in. Hope this helps... -- Kevin Backmann "JillyB" wrote: I urgently need to create a drop down list in excel with multiple choice selections - is this possible please? How do I do it . Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create multi choice selec drop down list in Excel ple
Hi Kevin,
Thanks for your reply. I need to be able to select more than one choice at a time from the drop down. Can this be done. e.g. Drop down list of areas, i.e. southampton, reading, basinbgstoke, heathrow and I need to selec t perhaps heathrow and reading at the same time from the drop down? "Kevin B" wrote: In a column to the right of your data, enter the selections you want to use in the drop down box, one per cell. Move to the cell where you want to start the drop down list and click DATA in the menu and select VALILDATION. Click the combobox drop down to display a list of allowed types and select LIST. Then in the SOURCE field, click the EXPAND dialog box on the right side of the text box, selet the range that has your list and click the COLLAPSE dialog box on the right side of the text box to return to the DATA VALIDATION dialog box. Click OK to complete and then copy the cell with the assigned data validation settings and paste in all the cells you want to use the list in. Hope this helps... -- Kevin Backmann "JillyB" wrote: I urgently need to create a drop down list in excel with multiple choice selections - is this possible please? How do I do it . Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create multi choice selec drop down list in Excel ple
You could use a listbox from the Forms toolbar and a button from the forms
toolbar to extract the choices. But you have to use code to get those selected values out of the listbox. I used the controls from the Forms toolbar (View|Toolbars|Forms) and added a listbox and a button to a worksheet. I rightclicked on the listbox and chose "Format Control" On the Control tab, I chose the Multi option and I assigned an Input Range (I used A1:A10 of the same sheet) Then I added this macro to a General module: Option Explicit Sub ExtractMyCities() Dim DestCell As Range Dim LBox As ListBox Dim iCtr As Long With ActiveSheet Set LBox = .ListBoxes("list box 1") Set DestCell = .Range("b1") DestCell.Resize(LBox.ListCount, 1).ClearContents End With For iCtr = 1 To LBox.ListCount If LBox.Selected(iCtr) = True Then DestCell.Value = LBox.List(iCtr) Set DestCell = DestCell.Offset(1, 0) End If Next iCtr End Sub Then I assigned this macro to the button. The selected values will clear the values in B1:B10 and then fill B1:B10 in order. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm JillyB wrote: Hi Kevin, Thanks for your reply. I need to be able to select more than one choice at a time from the drop down. Can this be done. e.g. Drop down list of areas, i.e. southampton, reading, basinbgstoke, heathrow and I need to selec t perhaps heathrow and reading at the same time from the drop down? "Kevin B" wrote: In a column to the right of your data, enter the selections you want to use in the drop down box, one per cell. Move to the cell where you want to start the drop down list and click DATA in the menu and select VALILDATION. Click the combobox drop down to display a list of allowed types and select LIST. Then in the SOURCE field, click the EXPAND dialog box on the right side of the text box, selet the range that has your list and click the COLLAPSE dialog box on the right side of the text box to return to the DATA VALIDATION dialog box. Click OK to complete and then copy the cell with the assigned data validation settings and paste in all the cells you want to use the list in. Hope this helps... -- Kevin Backmann "JillyB" wrote: I urgently need to create a drop down list in excel with multiple choice selections - is this possible please? How do I do it . Thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create multi choice selec drop down list in Excel ple
HI dave,
Thanks so much. I am a non starter with codee and forms. If I had a column headed office and then office locations listed below: eg. Col A Office Portsmouth Southampton Heathrow Birmingham How would the example you gave me be translated into this example please. Thanks very much Jill "Dave Peterson" wrote: You could use a listbox from the Forms toolbar and a button from the forms toolbar to extract the choices. But you have to use code to get those selected values out of the listbox. I used the controls from the Forms toolbar (View|Toolbars|Forms) and added a listbox and a button to a worksheet. I rightclicked on the listbox and chose "Format Control" On the Control tab, I chose the Multi option and I assigned an Input Range (I used A1:A10 of the same sheet) Then I added this macro to a General module: Option Explicit Sub ExtractMyCities() Dim DestCell As Range Dim LBox As ListBox Dim iCtr As Long With ActiveSheet Set LBox = .ListBoxes("list box 1") Set DestCell = .Range("b1") DestCell.Resize(LBox.ListCount, 1).ClearContents End With For iCtr = 1 To LBox.ListCount If LBox.Selected(iCtr) = True Then DestCell.Value = LBox.List(iCtr) Set DestCell = DestCell.Offset(1, 0) End If Next iCtr End Sub Then I assigned this macro to the button. The selected values will clear the values in B1:B10 and then fill B1:B10 in order. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm JillyB wrote: Hi Kevin, Thanks for your reply. I need to be able to select more than one choice at a time from the drop down. Can this be done. e.g. Drop down list of areas, i.e. southampton, reading, basinbgstoke, heathrow and I need to selec t perhaps heathrow and reading at the same time from the drop down? "Kevin B" wrote: In a column to the right of your data, enter the selections you want to use in the drop down box, one per cell. Move to the cell where you want to start the drop down list and click DATA in the menu and select VALILDATION. Click the combobox drop down to display a list of allowed types and select LIST. Then in the SOURCE field, click the EXPAND dialog box on the right side of the text box, selet the range that has your list and click the COLLAPSE dialog box on the right side of the text box to return to the DATA VALIDATION dialog box. Click OK to complete and then copy the cell with the assigned data validation settings and paste in all the cells you want to use the list in. Hope this helps... -- Kevin Backmann "JillyB" wrote: I urgently need to create a drop down list in excel with multiple choice selections - is this possible please? How do I do it . Thanks -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create multi choice selec drop down list in Excel ple
The only difference is that you'd specify A2:A5 as the input range.
Everything else would work the same. JillyB wrote: HI dave, Thanks so much. I am a non starter with codee and forms. If I had a column headed office and then office locations listed below: eg. Col A Office Portsmouth Southampton Heathrow Birmingham How would the example you gave me be translated into this example please. Thanks very much Jill "Dave Peterson" wrote: You could use a listbox from the Forms toolbar and a button from the forms toolbar to extract the choices. But you have to use code to get those selected values out of the listbox. I used the controls from the Forms toolbar (View|Toolbars|Forms) and added a listbox and a button to a worksheet. I rightclicked on the listbox and chose "Format Control" On the Control tab, I chose the Multi option and I assigned an Input Range (I used A1:A10 of the same sheet) Then I added this macro to a General module: Option Explicit Sub ExtractMyCities() Dim DestCell As Range Dim LBox As ListBox Dim iCtr As Long With ActiveSheet Set LBox = .ListBoxes("list box 1") Set DestCell = .Range("b1") DestCell.Resize(LBox.ListCount, 1).ClearContents End With For iCtr = 1 To LBox.ListCount If LBox.Selected(iCtr) = True Then DestCell.Value = LBox.List(iCtr) Set DestCell = DestCell.Offset(1, 0) End If Next iCtr End Sub Then I assigned this macro to the button. The selected values will clear the values in B1:B10 and then fill B1:B10 in order. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm JillyB wrote: Hi Kevin, Thanks for your reply. I need to be able to select more than one choice at a time from the drop down. Can this be done. e.g. Drop down list of areas, i.e. southampton, reading, basinbgstoke, heathrow and I need to selec t perhaps heathrow and reading at the same time from the drop down? "Kevin B" wrote: In a column to the right of your data, enter the selections you want to use in the drop down box, one per cell. Move to the cell where you want to start the drop down list and click DATA in the menu and select VALILDATION. Click the combobox drop down to display a list of allowed types and select LIST. Then in the SOURCE field, click the EXPAND dialog box on the right side of the text box, selet the range that has your list and click the COLLAPSE dialog box on the right side of the text box to return to the DATA VALIDATION dialog box. Click OK to complete and then copy the cell with the assigned data validation settings and paste in all the cells you want to use the list in. Hope this helps... -- Kevin Backmann "JillyB" wrote: I urgently need to create a drop down list in excel with multiple choice selections - is this possible please? How do I do it . Thanks -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create multi choice selec drop down list in Excel please
DataValidationList.
You can type the values in comma-delimited or use a pre-existing range of cells in a column with the values. Note: if using a pre-existing range, if you give that range a name, the list can be on another worksheet. Gord Dibben MS Excel MVP On Mon, 3 Mar 2008 06:37:01 -0800, JillyB wrote: I urgently need to create a drop down list in excel with multiple choice selections - is this possible please? How do I do it . Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create multi choice selec drop down list in Excel ple
Debra Dalgleish has a sample workbook with DV dropdown list and code to allow
multiple selections fro a list. DV0017 - Select Multiple Items from Dropdown List-- Select multiple items from a dropdown list; an event macro stores selections in adjacent cell, or in same cell. DataValMultiSelect.zip 18kb updated 22-Feb-07 http://www.contextures.on.ca/excelfiles.html#DataVal Gord Dibben MS Excel MVP On Mon, 3 Mar 2008 07:27:01 -0800, JillyB wrote: Hi Kevin, Thanks for your reply. I need to be able to select more than one choice at a time from the drop down. Can this be done. e.g. Drop down list of areas, i.e. southampton, reading, basinbgstoke, heathrow and I need to selec t perhaps heathrow and reading at the same time from the drop down? "Kevin B" wrote: In a column to the right of your data, enter the selections you want to use in the drop down box, one per cell. Move to the cell where you want to start the drop down list and click DATA in the menu and select VALILDATION. Click the combobox drop down to display a list of allowed types and select LIST. Then in the SOURCE field, click the EXPAND dialog box on the right side of the text box, selet the range that has your list and click the COLLAPSE dialog box on the right side of the text box to return to the DATA VALIDATION dialog box. Click OK to complete and then copy the cell with the assigned data validation settings and paste in all the cells you want to use the list in. Hope this helps... -- Kevin Backmann "JillyB" wrote: I urgently need to create a drop down list in excel with multiple choice selections - is this possible please? How do I do it . Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Multi-select from drop down list in Excel 97 | Excel Discussion (Misc queries) | |||
Drop Down List choice selecting another drop down list | Excel Worksheet Functions | |||
drop-down list changing according to choice made | Excel Worksheet Functions | |||
How do i create a multiple choice drop down list | Charts and Charting in Excel |