Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
sriramus
 
Posts: n/a
Default multiple select from the drop down list in excel. list in one sheet and drop down in


Hi,

i want to include multiple select in my drop down list which is created
in excel.

i have the list in sheet 2 and the drop down appears in sheet 1.

i got to know this can be done with a macro and there was some
information but it wasnt useful for me.

can any one please help me with this , if i need to write a macro, how
do i go about doing that.

-The steps i followed to create the drop down are. (i followed the
below link)

dhttp://spreadsheets.about.com/od/exceltutorialsandtips/ss/blexdropboxes_6.htm

1. Used a new worksheet for lists. Entered the data for the list in a
column.
Once entered all the data for the list, selected the data.

2. In the "Insert" menu, selected "Name" then "Define".

3. In the box under "Names in Workbook", entered the name for the
range. saw the range selected in the "Refers to:" box. Clicked "Add".
Clicked "OK" to close the window.

4. went to worksheet where i want the drop down box to appear. Made
the active cell the one where i want the list to appear . In the
"Data" menu, selected "Validation".

5. From the "Allow:" drop down box, selected "List". A new selection
appeared- "Source:". In that box typed "=" and then the name of my
range. "In-cell Dropdown" box was ticked. Clicked "OK" .

6. When i clicked in the cell that selected, i see a drop down box
with list appearing.-


--
sriramus
------------------------------------------------------------------------
sriramus's Profile: http://www.excelforum.com/member.php...o&userid=28208
View this thread: http://www.excelforum.com/showthread...hreadid=478718

  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default multiple select from the drop down list in excel. list in one sheetand drop down in

I think I'd use something different if I wanted to select multiple items.

I put a listbox from the control toolbox toolbar on worksheet 1. I put a
commandbutton from that same control toolbox toolbar right next to it.

Then I added this to the ThisWorkbook module to populate that listbox each time
the workbook was opened.

Option Explicit
Private Sub Workbook_Open()
With Me.Worksheets("Sheet1").ListBox1
.MultiSelect = fmMultiSelectMulti
.List = Me.Worksheets("sheet2").Range("a1:a10").Value
End With
End Sub


Then I double clicked on that commandbutton on sheet1 and added this code to the
code window:

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim OutputCell As Range

Set OutputCell = Me.Range("C1")
OutputCell.Resize(Me.ListBox1.ListCount).ClearCont ents

For iCtr = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCtr) = True Then
OutputCell.Value = Me.ListBox1.List(iCtr)
Set OutputCell = OutputCell.Offset(1, 0)
End If
Next iCtr
End Sub

You select as many things in the listbox as you want. When you're done you
click the button and C1 (and below get populated with your choices.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

sriramus wrote:

Hi,

i want to include multiple select in my drop down list which is created
in excel.

i have the list in sheet 2 and the drop down appears in sheet 1.

i got to know this can be done with a macro and there was some
information but it wasnt useful for me.

can any one please help me with this , if i need to write a macro, how
do i go about doing that.

-The steps i followed to create the drop down are. (i followed the
below link)

dhttp://spreadsheets.about.com/od/exceltutorialsandtips/ss/blexdropboxes_6.htm

1. Used a new worksheet for lists. Entered the data for the list in a
column.
Once entered all the data for the list, selected the data.

2. In the "Insert" menu, selected "Name" then "Define".

3. In the box under "Names in Workbook", entered the name for the
range. saw the range selected in the "Refers to:" box. Clicked "Add".
Clicked "OK" to close the window.

4. went to worksheet where i want the drop down box to appear. Made
the active cell the one where i want the list to appear . In the
"Data" menu, selected "Validation".

5. From the "Allow:" drop down box, selected "List". A new selection
appeared- "Source:". In that box typed "=" and then the name of my
range. "In-cell Dropdown" box was ticked. Clicked "OK" .

6. When i clicked in the cell that selected, i see a drop down box
with list appearing.-

--
sriramus
------------------------------------------------------------------------
sriramus's Profile: http://www.excelforum.com/member.php...o&userid=28208
View this thread: http://www.excelforum.com/showthread...hreadid=478718


--

Dave Peterson
  #3   Report Post  
grahammal
 
Posts: n/a
Default multiple select from the drop down list in excel. list in one sheet and drop down in


Could try the following link, may be of some help.

http://www.contextures.on.ca/xlDataVal02.html


--
grahammal
------------------------------------------------------------------------
grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
View this thread: http://www.excelforum.com/showthread...hreadid=478718

  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default multiple select from the drop down list in excel. list in one sheetand drop down in

This would help if the OP wanted to have dependent lists for multiple cells with
data|validation. But I'm not sure how this would work with selecting multiple
items.

grahammal wrote:

Could try the following link, may be of some help.

http://www.contextures.on.ca/xlDataVal02.html

--
grahammal
------------------------------------------------------------------------
grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
View this thread: http://www.excelforum.com/showthread...hreadid=478718


--

Dave Peterson
  #5   Report Post  
sriramus
 
Posts: n/a
Default multiple select from the drop down list in excel. list in one sheet and drop down in


Many Thanks for your precious time to reply.

I implemented the below procedure but i dont want the result of select
to appear in C1 and also if i have multiple drop down lists with
multiple select in the same work sheet, i dont know how to procede.

i would appreciate if u can guide me to do the above task.


Once again Thanks a lot.





Dave Peterson Wrote:
I think I'd use something different if I wanted to select multiple
items.

I put a listbox from the control toolbox toolbar on worksheet 1. I put
a
commandbutton from that same control toolbox toolbar right next to it.

Then I added this to the ThisWorkbook module to populate that listbox
each time
the workbook was opened.

Option Explicit
Private Sub Workbook_Open()
With Me.Worksheets("Sheet1").ListBox1
.MultiSelect = fmMultiSelectMulti
.List = Me.Worksheets("sheet2").Range("a1:a10").Value
End With
End Sub


Then I double clicked on that commandbutton on sheet1 and added this
code to the
code window:

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim OutputCell As Range

Set OutputCell = Me.Range("C1")
OutputCell.Resize(Me.ListBox1.ListCount).ClearCont ents

For iCtr = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCtr) = True Then
OutputCell.Value = Me.ListBox1.List(iCtr)
Set OutputCell = OutputCell.Offset(1, 0)
End If
Next iCtr
End Sub

You select as many things in the listbox as you want. When you're done
you
click the button and C1 (and below get populated with your choices.

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

sriramus wrote:

Hi,

i want to include multiple select in my drop down list which is

created
in excel.

i have the list in sheet 2 and the drop down appears in sheet 1.

i got to know this can be done with a macro and there was some
information but it wasnt useful for me.

can any one please help me with this , if i need to write a macro,

how
do i go about doing that.

-The steps i followed to create the drop down are. (i followed the
below link)


dhttp://spreadsheets.about.com/od/exceltutorialsandtips/ss/blexdropboxes_6.htm

1. Used a new worksheet for lists. Entered the data for the list in

a
column.
Once entered all the data for the list, selected the data.

2. In the "Insert" menu, selected "Name" then "Define".

3. In the box under "Names in Workbook", entered the name for the
range. saw the range selected in the "Refers to:" box. Clicked

"Add".
Clicked "OK" to close the window.

4. went to worksheet where i want the drop down box to appear.

Made
the active cell the one where i want the list to appear . In the
"Data" menu, selected "Validation".

5. From the "Allow:" drop down box, selected "List". A new

selection
appeared- "Source:". In that box typed "=" and then the name of my
range. "In-cell Dropdown" box was ticked. Clicked "OK" .

6. When i clicked in the cell that selected, i see a drop down

box
with list appearing.-

--
sriramus

------------------------------------------------------------------------
sriramus's Profile:

http://www.excelforum.com/member.php...o&userid=28208
View this thread:

http://www.excelforum.com/showthread...hreadid=478718

--

Dave Peterson



--
sriramus
------------------------------------------------------------------------
sriramus's Profile: http://www.excelforum.com/member.php...o&userid=28208
View this thread: http://www.excelforum.com/showthread...hreadid=478718



  #6   Report Post  
sriramus
 
Posts: n/a
Default multiple select from the drop down list in excel. list in one sheet and drop down in


Thanks a lot For the Information and your valuable time spent for me


grahammal Wrote:
Could try the following link, may be of some help.

http://www.contextures.on.ca/xlDataVal02.html



--
sriramus
------------------------------------------------------------------------
sriramus's Profile: http://www.excelforum.com/member.php...o&userid=28208
View this thread: http://www.excelforum.com/showthread...hreadid=478718

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Move data from one sheet to another using drop down lists. pwtechmn Excel Worksheet Functions 3 October 16th 05 12:36 AM
How do i create a multiple choice drop down list Piper Charts and Charting in Excel 3 September 8th 05 12:40 AM
circular drop down list on different pages Cbh35711 Excel Worksheet Functions 0 August 16th 05 06:11 PM
Data Validation using List (But needs unique list in drop down lis Tan New Users to Excel 1 July 8th 05 03:32 PM
changing value of a cell by selecting an item from a drop down list Bobby Mir Excel Worksheet Functions 6 June 8th 05 08:33 PM


All times are GMT +1. The time now is 06:33 AM.

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

About Us

"It's about Microsoft Excel"