+ Reply to Thread
Results 1 to 6 of 6

multiple select from the drop down list in excel. list in one sheet and drop down in

  1. #1
    Registered User
    Join Date
    10-19-2005
    Posts
    3

    Post 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.

  2. #2
    Dave Peterson
    Guest

    Re: 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).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

  3. #3
    Forum Contributor
    Join Date
    02-24-2005
    Posts
    154

    Drop down lists

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

    http://www.contextures.on.ca/xlDataVal02.html
    Last edited by grahammal; 10-24-2005 at 11:34 AM.

  4. #4
    Dave Peterson
    Guest

    Re: 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. #5
    Registered User
    Join Date
    10-19-2005
    Posts
    3

    Thank You but still have problem

    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.





    Quote Originally Posted by Dave Peterson
    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

  6. #6
    Registered User
    Join Date
    10-19-2005
    Posts
    3

    Thank you

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


    Quote Originally Posted by grahammal
    Could try the following link, may be of some help.

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1