469,315 Members | 2,178 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

Ms Excel drop down box

553 512MB
Hi

Can anyone tell me how to create a drop-down box in Ms Excel please.

I simple one which can have in it some values.

Hope you understand.. i am not sure what is the difference between drop-down menu/box and combo box. but it is reuqired in ms excel.

thanks
user__786
Mar 23 '07 #1
18 17417
ADezii
8,800 Expert 8TB
Hi

Can anyone tell me how to create a drop-down box in Ms Excel please.

I simple one which can have in it some values.

Hope you understand.. i am not sure what is the difference between drop-down menu/box and combo box. but it is reuqired in ms excel.

thanks
user__786
Within your Excel Worksheet:
__1. View ==> Toolbars ==> Control Toolbox
__2. Select and Draw a Combo Box on the Worksheet
__3. ALT+F11 to go to the Visual Basic Window
__4. In the Worksheet_Activate() Event place the following code
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Activate()
  2.   ComboBox1.Clear
  3.   ComboBox1.AddItem "Philadelphia"
  4.   ComboBox1.AddItem "New York"
  5.   ComboBox1.AddItem "San Francisco"
  6.   ComboBox1.AddItem "Kansas City"
  7. End Sub
__5. ALT+F11 to return to the Worksheet
__6. If you are still in Design Mode, press the Design Mode Icon in the Control Toolbox
__7. Activate the Worksheet where you placed the Combo Box
__8. The 4 cities will now be listed
__9. Any clarification that you may need, please let me know
Mar 24 '07 #2
It might work better if you use the text validation feature. Then your user can select on the actual cell, and choose from a list.

to do this:


some where on the sheet where the user is unlikely to find it enter the options you want them to use. Be sure to either leave a blank cell, or use "select from list" in that cell so you don't have a default value that is an actual value.

Now,
  • click on Data in the Menu Bar
  • click on Validation...
    the wizard will open
  • Select In the Settings Tab
  • Select List from the Allow drop down
  • to the right 2 new boxes will appear - if you used a blank in your list de-slect ignore blanks
  • click the button at the end of the Source box, so you can select the list of data you entered earlier. Be sure to select the blank cell or select from list.

You can create a message for the user in the Input Message Tab and you can put an error message for the user in the Error Alert Tab - in the event they try to enter something that is not in your list.

when you are finished with the messages, click ok.

Now when the user clicks on the cell - a drop down arrow will appear, allowing them to select from the list you created.

Hope this helps
Sophie
Mar 24 '07 #3
questionit
553 512MB
Thanks Sophie very much and ADezii


ADezii ..
What you mean by : Worksheet_Activate()

When i press ALT+ F11 i goto VB code editor. I can see available sheets that i have on my Ms Excel Worksbook. I selct the sheet that i am currently working on.

For example, this is what i see on bar on top:
Book1.xls - Sheet1 (Code)

I have tried exacly with your method, when i come back to Excel and go out of Design mode, i see no Values in the Combo Box.

any ideas !!

Thanks
Mar 24 '07 #4
ADezii
8,800 Expert 8TB
Thanks Sophie very much and ADezii


ADezii ..
What you mean by : Worksheet_Activate()

When i press ALT+ F11 i goto VB code editor. I can see available sheets that i have on my Ms Excel Worksbook. I selct the sheet that i am currently working on.

For example, this is what i see on bar on top:
Book1.xls - Sheet1 (Code)

I have tried exacly with your method, when i come back to Excel and go out of Design mode, i see no Values in the Combo Box.

any ideas !!

Thanks
After pressiing ALT + F11, press the F7 key in case the Code Window is not visible. You will see 2 Combo Boxes at the Top of the Window. In the left Combo select Worksheet and in the right Combo select Activate. You should now be in the Activate Event of the current Worksheet.
Mar 25 '07 #5
questionit
553 512MB
Hi Adezii

I have done exactly how you have exaplined. But when i go back to Excel and come out of Design mode and check the combox box - there is no listing of items. It is empty as before.

this is the code i have put:
Private Sub Worksheet_Activate()
ComboBox1.Clear
ComboBox1.AddItem "Philadelphia"
ComboBox1.AddItem "New York"
ComboBox1.AddItem "San Francisco"
ComboBox1.AddItem "Kansas City"
End Sub

And i also save file after putting this code in.

Anything i might have been doing wrong?

Also, i was wondering, can we not do this in the ComboBox1 properties instead of Worksheet properties?

Thanks
Mar 26 '07 #6
questionit
553 512MB
Hello any answer please ?
Mar 29 '07 #7
ADezii
8,800 Expert 8TB
Hi Adezii

I have done exactly how you have exaplined. But when i go back to Excel and come out of Design mode and check the combox box - there is no listing of items. It is empty as before.

this is the code i have put:
Private Sub Worksheet_Activate()
ComboBox1.Clear
ComboBox1.AddItem "Philadelphia"
ComboBox1.AddItem "New York"
ComboBox1.AddItem "San Francisco"
ComboBox1.AddItem "Kansas City"
End Sub

And i also save file after putting this code in.

Anything i might have been doing wrong?

Also, i was wondering, can we not do this in the ComboBox1 properties instead of Worksheet properties?

Thanks
Replace ComboBox1 with your Combo Box Name.
Mar 30 '07 #8
questionit
553 512MB
Hello ADezii

I have put my Combox box name corrctly. Still no success.

Could you send me a sample working code please : tinythings@msn.com

Thanks
Mar 31 '07 #9
questionit
553 512MB
Hello ADezii

It is working now.. You did not tell me that i need to compile the VB code.

It is all working now.

Now if i select one of the values from Combo-Box, how can i show on a particular cell in Excel sheet some value according to the value i select in Combox box?


Thanks a lot
Mar 31 '07 #10
ADezii
8,800 Expert 8TB
Hello ADezii

It is working now.. You did not tell me that i need to compile the VB code.

It is all working now.

Now if i select one of the values from Combo-Box, how can i show on a particular cell in Excel sheet some value according to the value i select in Combox box?


Thanks a lot
Expand|Select|Wrap|Line Numbers
  1. Private Sub ComboBox1_Change()
  2.   Cells(10, 6).Value = ComboBox1.Value
  3. End Sub
Mar 31 '07 #11
questionit
553 512MB
Expand|Select|Wrap|Line Numbers
  1. Private Sub ComboBox1_Change()
  2.   Cells(10, 6).Value = ComboBox1.Value
  3. End Sub
this is fine but i need to set different values e.g:

If ComboBox1.Value = "New York"
Cells(10,6).Value = "Any value"

..

similarly with all the ComboBox values.. please demonstrate how to do the If-Then code in VB for this.

Thanks
Apr 1 '07 #12
questionit
553 512MB
this is fine but i need to set different values e.g:

If ComboBox1.Value = "New York"
Cells(10,6).Value = "Any value"

..

similarly with all the ComboBox values.. please demonstrate how to do the If-Then code in VB for this.

Thanks
any help please ?
Apr 2 '07 #13
ADezii
8,800 Expert 8TB
Hello ADezii

It is working now.. You did not tell me that i need to compile the VB code.

It is all working now.

Now if i select one of the values from Combo-Box, how can i show on a particular cell in Excel sheet some value according to the value i select in Combox box?


Thanks a lot
Compiling the code is not the issue - you must re-activate the WorkSheet in order for the Combo Box to populate.
Apr 4 '07 #14
questionit
553 512MB
Compiling the code is not the issue - you must re-activate the WorkSheet in order for the Combo Box to populate.
Ok,thanks.

similarly with all the ComboBox values.. please demonstrate how to do the If-Then code in VB for this. E.g:
If combox.Value = New york" THEN
Cells(10,5).Value = 100
....
i dont knowVB programming.

Thanks
Apr 4 '07 #15
SammyB
807 Expert 512MB
i dont knowVB programming.
Then it would be easier to not use code. If you use Data Validation as Scolivas suggested, you do not need any code. Try this simple example:
  1. Open a new Excel workbook and enter New York, Chicago, & Hong Kong in A1, A2 & A3. Also enter the numbers associated with these cities, say, 100, 50 & 30 in B1, B2 & B3.
  2. Click on A10. Menu: Data, Validation. Allow:List. Source: =A1:A3
  3. Press OK
  4. Click on the drop-down arrow and select New York.
  5. Click on B10 and enter this formula: =VLOOKUP(A10,DB,2,FALSE)
Isn't that easier than programming?
Apr 4 '07 #16
questionit
553 512MB
Then it would be easier to not use code. If you use Data Validation as Scolivas suggested, you do not need any code. Try this simple example:
  1. Open a new Excel workbook and enter New York, Chicago, & Hong Kong in A1, A2 & A3. Also enter the numbers associated with these cities, say, 100, 50 & 30 in B1, B2 & B3.
  2. Click on A10. Menu: Data, Validation. Allow:List. Source: =A1:A3
  3. Press OK
  4. Click on the drop-down arrow and select New York.
  5. Click on B10 and enter this formula: =VLOOKUP(A10,DB,2,FALSE)
Isn't that easier than programming?
Adezzi

Thanks, that is easy but i want to do it in programming. I have knowledge of C/C++ so it would be much easier and consistent if i learn this in VB.
Can you tell me how to do that in VB

Thanks
Apr 11 '07 #17
ADezii
8,800 Expert 8TB
Ok,thanks.

similarly with all the ComboBox values.. please demonstrate how to do the If-Then code in VB for this. E.g:
If combox.Value = New york" THEN
Cells(10,5).Value = 100
....
i dont knowVB programming.

Thanks
Expand|Select|Wrap|Line Numbers
  1. Private Sub ComboBox1_Change()
  2. Select Case ComboBox1.Value
  3.   Case "New York"
  4.     Cells(1, 2).Value = "You chose New York"
  5.   Case "Philadelphia"
  6.     Cells(3, 2).Value = "You chose Philadelphia"
  7.   Case "San Francisco"
  8.     Cells(5, 2).Value = "You chose San Francisco"""
  9.   Case "Kansas City"
  10.     Cells(7, 2).Value = "You chose Kansas City"
  11. End Select
  12. End Sub
Apr 11 '07 #18
questionit
553 512MB
Expand|Select|Wrap|Line Numbers
  1. Private Sub ComboBox1_Change()
  2. Select Case ComboBox1.Value
  3.   Case "New York"
  4.     Cells(1, 2).Value = "You chose New York"
  5.   Case "Philadelphia"
  6.     Cells(3, 2).Value = "You chose Philadelphia"
  7.   Case "San Francisco"
  8.     Cells(5, 2).Value = "You chose San Francisco"""
  9.   Case "Kansas City"
  10.     Cells(7, 2).Value = "You chose Kansas City"
  11. End Select
  12. End Sub
Thanks a lot Adezzi
Apr 14 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Bryan Harrington | last post: by
13 posts views Thread by Hemant Sipahimalani | last post: by
reply views Thread by =?Utf-8?B?c2hyZWU=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.