Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Multiple items from drop down list
Hi guys I am trying to create a student register.
Right now I have 3 columns; "Student Name", "Number of Subjects registered" and "Subjects Registered". The students' names are entered manually. The number of subjects is selected from a drop down list, giving selection options from 1 to 6. I would like help with the "Subjects Registered" column. There are only a maximum of 6 fixed subjects that a student can register for, English, Math, History, Chemistry, Physics and Accounts. Is there a way I can put this in a drop down list and select multiple items, maybe by checking boxes so that they are all listed in a cell? Or is there some other way to do this apart from drop down lists? I would appreciate any help offered. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Multiple items from drop down list
Hi,
Try this. Open the 'Control' toolbox and put a listbox and a button on you sheet. Ensuring your in 'Design' mode right click the list box and set these properties ListFillRange=A1:A6 MultiSelect=fmMultiSelectMulti ListStyle=fmListStyleOption For the listfill range use whatever you want. The listbox is now set up. Enter you 6 categories in a1 - A6 Still in design mode double click the button and paste the code below in. Exit design mode and make your selections and they will be transfeered to column B. Private Sub CommandButton1_Click() Dim Selected As Long Dim Checked As Boolean Dim LastRow As Long For Selected = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(Selected) Then Checked = True LastRow = Cells(Rows.Count, "B").End(xlUp).Row Range("B" & LastRow + 1) = ListBox1.List(Selected) ListBox1.Selected(Selected) = False End If Next Selected If Checked = False Then MsgBox "Nothing Selected" End If End Sub Mike "prem" wrote: Hi guys I am trying to create a student register. Right now I have 3 columns; "Student Name", "Number of Subjects registered" and "Subjects Registered". The students' names are entered manually. The number of subjects is selected from a drop down list, giving selection options from 1 to 6. I would like help with the "Subjects Registered" column. There are only a maximum of 6 fixed subjects that a student can register for, English, Math, History, Chemistry, Physics and Accounts. Is there a way I can put this in a drop down list and select multiple items, maybe by checking boxes so that they are all listed in a cell? Or is there some other way to do this apart from drop down lists? I would appreciate any help offered. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Multiple items from drop down list
Hi Mike,
I am using Excel 2007. When you mention, "right click the list box and set these properties ListFillRange=A1:A6 MultiSelect=fmMultiSelectMulti ListStyle=fmListStyleOption" should I key it in under "assign macro" option? "Mike H" wrote: Hi, Try this. Open the 'Control' toolbox and put a listbox and a button on you sheet. Ensuring your in 'Design' mode right click the list box and set these properties ListFillRange=A1:A6 MultiSelect=fmMultiSelectMulti ListStyle=fmListStyleOption For the listfill range use whatever you want. The listbox is now set up. Enter you 6 categories in a1 - A6 Still in design mode double click the button and paste the code below in. Exit design mode and make your selections and they will be transfeered to column B. Private Sub CommandButton1_Click() Dim Selected As Long Dim Checked As Boolean Dim LastRow As Long For Selected = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(Selected) Then Checked = True LastRow = Cells(Rows.Count, "B").End(xlUp).Row Range("B" & LastRow + 1) = ListBox1.List(Selected) ListBox1.Selected(Selected) = False End If Next Selected If Checked = False Then MsgBox "Nothing Selected" End If End Sub Mike "prem" wrote: Hi guys I am trying to create a student register. Right now I have 3 columns; "Student Name", "Number of Subjects registered" and "Subjects Registered". The students' names are entered manually. The number of subjects is selected from a drop down list, giving selection options from 1 to 6. I would like help with the "Subjects Registered" column. There are only a maximum of 6 fixed subjects that a student can register for, English, Math, History, Chemistry, Physics and Accounts. Is there a way I can put this in a drop down list and select multiple items, maybe by checking boxes so that they are all listed in a cell? Or is there some other way to do this apart from drop down lists? I would appreciate any help offered. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Multiple items from drop down list
Hi Mike,
when you mentioned "Ensuring your in 'Design' mode right click the list box and set these properties ListFillRange=A1:A6 MultiSelect=fmMultiSelectMulti ListStyle=fmListStyleOption" am I supposed to key this in under the "assign macro" section? Also my design mode button seems to be greyed out. i am using Excel 2007. Thank you for you help up till now. "Mike H" wrote: Hi, Try this. Open the 'Control' toolbox and put a listbox and a button on you sheet. Ensuring your in 'Design' mode right click the list box and set these properties ListFillRange=A1:A6 MultiSelect=fmMultiSelectMulti ListStyle=fmListStyleOption For the listfill range use whatever you want. The listbox is now set up. Enter you 6 categories in a1 - A6 Still in design mode double click the button and paste the code below in. Exit design mode and make your selections and they will be transfeered to column B. Private Sub CommandButton1_Click() Dim Selected As Long Dim Checked As Boolean Dim LastRow As Long For Selected = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(Selected) Then Checked = True LastRow = Cells(Rows.Count, "B").End(xlUp).Row Range("B" & LastRow + 1) = ListBox1.List(Selected) ListBox1.Selected(Selected) = False End If Next Selected If Checked = False Then MsgBox "Nothing Selected" End If End Sub Mike "prem" wrote: Hi guys I am trying to create a student register. Right now I have 3 columns; "Student Name", "Number of Subjects registered" and "Subjects Registered". The students' names are entered manually. The number of subjects is selected from a drop down list, giving selection options from 1 to 6. I would like help with the "Subjects Registered" column. There are only a maximum of 6 fixed subjects that a student can register for, English, Math, History, Chemistry, Physics and Accounts. Is there a way I can put this in a drop down list and select multiple items, maybe by checking boxes so that they are all listed in a cell? Or is there some other way to do this apart from drop down lists? I would appreciate any help offered. Thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Multiple items from drop down list
Not sure whether you're still onto this thread ...
but think you could also try Debra Dalgleish's sample file at: http://www.contextures.com/excelfiles.html Under Data Validation, look for: DV0017 - Select Multiple Items from Dropdown List It's a very informative sample where several techniques are illustrated in different tabs Since you want the "multi-selected" subjects to be ... all listed in a cell? the best fit, IMO, is Debra's example in the tab: CommaSeparated -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Multiple items from drop down list
"Max" wrote: Not sure whether you're still onto this thread ... but think you could also try Debra Dalgleish's sample file at: http://www.contextures.com/excelfiles.html Under Data Validation, look for: DV0017 - Select Multiple Items from Dropdown List It's a very informative sample where several techniques are illustrated in different tabs Since you want the "multi-selected" subjects to be ... all listed in a cell? the best fit, IMO, is Debra's example in the tab: CommaSeparated -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- Thank you Max. I left that project alone for awhile. Will get back to it and let you know how it went. Thank you very much for this information. Regards, Prem |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically selecting items in drop down list | Excel Discussion (Misc queries) | |||
Choosing multiple items in a drop down list | Excel Discussion (Misc queries) | |||
selecting multiple items from drop-down list | Excel Discussion (Misc queries) | |||
selecting multiple items in an excel drop down list | Excel Discussion (Misc queries) | |||
Selecting multiple items from a drop down list | Excel Discussion (Misc queries) |