A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I create multi choice selec drop down list in Excel please



 
 
Thread Tools Display Modes
  #1  
Old March 3rd 08, 02:37 PM posted to microsoft.public.excel.misc
JillyB
external usenet poster
 
Posts: 10
Default 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

Ads
  #2  
Old March 3rd 08, 02:52 PM posted to microsoft.public.excel.misc
Kevin B
external usenet poster
 
Posts: 1,317
Default 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  
Old March 3rd 08, 03:27 PM posted to microsoft.public.excel.misc
JillyB
external usenet poster
 
Posts: 10
Default 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  
Old March 3rd 08, 04:28 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default 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  
Old March 3rd 08, 05:41 PM posted to microsoft.public.excel.misc
JillyB
external usenet poster
 
Posts: 10
Default 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  
Old March 3rd 08, 07:17 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default 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  
Old March 3rd 08, 09:30 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,912
Default How do I create multi choice selec drop down list in Excel please

Data>Validation>List.

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  
Old March 3rd 08, 09:41 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,912
Default 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
>> >


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Drop Down/List w/Code and Definition, only code entered when selec Spiritdancer Excel Worksheet Functions 2 November 2nd 07 03:57 AM
Multi-select from drop down list in Excel 97 arpgis Excel Discussion (Misc queries) 1 April 11th 07 10:46 PM
Drop Down List choice selecting another drop down list CVD0722 Excel Worksheet Functions 3 October 31st 06 01:02 PM
drop-down list changing according to choice made choc_penguin Excel Worksheet Functions 1 January 4th 06 06:26 PM
How do i create a multiple choice drop down list Piper Charts and Charting in Excel 3 September 8th 05 12:40 AM


All times are GMT +1. The time now is 10:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.