Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 45
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 45
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 45
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 45
Default 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
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
automatically selecting items in drop down list Tom Mucciolo Excel Discussion (Misc queries) 5 September 2nd 08 04:31 AM
Choosing multiple items in a drop down list Shad Excel Discussion (Misc queries) 9 July 7th 08 06:38 PM
selecting multiple items from drop-down list [email protected] Excel Discussion (Misc queries) 1 October 25th 07 01:24 PM
selecting multiple items in an excel drop down list karen f Excel Discussion (Misc queries) 4 August 13th 07 08:22 AM
Selecting multiple items from a drop down list Jeremy Excel Discussion (Misc queries) 4 December 11th 06 03:53 PM


All times are GMT +1. The time now is 12:46 PM.

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"