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).ClearContents
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
Bookmarks