473,718 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Ms Excel drop down box

553 Contributor
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 17944
ADezii
8,834 Recognized Expert Expert
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_Activ ate() 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
scolivas
56 New Member
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 Contributor
Thanks Sophie very much and ADezii


ADezii ..
What you mean by : Worksheet_Activ ate()

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,834 Recognized Expert Expert
Thanks Sophie very much and ADezii


ADezii ..
What you mean by : Worksheet_Activ ate()

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 Contributor
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_Activ ate()
ComboBox1.Clear
ComboBox1.AddIt em "Philadelph ia"
ComboBox1.AddIt em "New York"
ComboBox1.AddIt em "San Francisco"
ComboBox1.AddIt em "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 Contributor
Hello any answer please ?
Mar 29 '07 #7
ADezii
8,834 Recognized Expert Expert
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_Activ ate()
ComboBox1.Clear
ComboBox1.AddIt em "Philadelph ia"
ComboBox1.AddIt em "New York"
ComboBox1.AddIt em "San Francisco"
ComboBox1.AddIt em "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 Contributor
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 Contributor
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

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

Similar topics

2
1807
by: Bryan Harrington | last post by:
Hello all.. I'm working on some reports, and have added the ability to "download" the reports to excel. Not a big deal, fairly straight forward. However, PHB wants to be able to create some pivot tables from said reports (again.. no biggie) and when he goes to save the file, Excel wants to save the file as a web page instead of and Excel spreadsheet. Now.. I know the simple thing here is to just pick Excel from the drop down box in the...
1
5847
by: Glen Vermeylen | last post by:
Hi, For a project at school we have to automate the assignment of seats in classrooms to students during the exams. The lady who previously did everything manually kept the layouts of the classrooms in an excel-document: 1 sheet per classroom, and she marked the cells which represent the seats with a border. The problem however is that she sometimes merged cells together to get a better layout.
13
13231
by: Hemant Sipahimalani | last post by:
The following piece of code is being used to export HTML to excel. HttpContext.Current.Response.ContentType = "application/vnd.ms-excel" HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=ABC.xls") HttpContext.Current.Response.Write(strHTML) HttpContext.Current.Response.End() However when the user tries to save it the Default File Type is Web Page(*.htm; *.html)
1
5676
by: BaaZauq | last post by:
Hello everybody! I'm not a master of clientside JavaScript but I have much working knowledge of it. Here is my problem : I have a web-page with a "text-area" box. I have an excel file with 10 columns and 15 rows. each cell has a unicode-text data stored in it. I want to show two drop-down menus along side with the above said text-area.
1
2582
by: datttanand | last post by:
how to add drop down list in excel cell using javascript code?
1
1455
by: Tlholo | last post by:
Here are the requirements for the project. I have also included the other project that I am working on and is also giving me some problems. i need code examples. Project 1 This program actually has 4 parts that I have to do. There is a master database and an excel file that has to be downloaded from the server once a week 1.design a piece of code that will make this program run or download the file from the server once a week, let’s...
2
1797
by: ConieFL | last post by:
I have and excel spreadsheet with a custom toolbar. On the end of the custom tool bar is a down arrow for a pull down menu which displays 'Add or Remove Buttons' Is there a way not to display the 'Add or Remove Buttons' drop down on a user created custom toolbar?
0
2021
by: sandeepsangshetty | last post by:
How do I create a drop down list which has the values from a database column. So I need this list to have values like" "Select distinct departmentname from Department" The Department name should show in the drop down list and I should be able to select one department name from the list. This is my code for read the column name from an excet test11.xls.now i want to fetch the dataset to that column name departmant. please help.
0
1513
by: =?Utf-8?B?c2hyZWU=?= | last post by:
Hi All, I want to add a drop down to a cell in excel using C#. Can some one suggest how can I achieve that. Thanks, Shrish
0
8724
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9358
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9208
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9053
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7991
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6654
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5971
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4481
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
2123
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.