Solved

excel use drop down in form

Posted on 2013-06-02
12
167 Views
Last Modified: 2013-12-29
In Excel 2013 I'm using a form to enter data. I'd like to use a dropdown list for various  entries on the form.

Any help would be appreciated.

Thanks
Ed
0
Comment
Question by:lifeactuary
  • 5
  • 5
12 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39216097
DO you see the DEVELOPER tab when you have a workbook open?

If not RIGHT CLICK the ribbon
Click CUSTOMIZE RIBBON
Add the DEVELOPER menu

Use the INSERT drop down to add objects like that
From the insert drop down
Click the control
Draw it on the worksheet
Right click it to change the properties

mlmcc
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39216890
There are several choices in Excel.  One is as mentioned above, where you can add one of two custom dropdowns ON TOP OF Excel.

A more convenience way is to add Data Validation which, when the user clicks in a cell, gives a dropdown button next to the cell with a list of predefined choices.

To do that, click on the Data menu-tab then in the Data Tools group, click on the Data Validation item.  You'll get the Data Validation dialog box.  In the Settings tab (first one) under "Allow" change "Any value" to "List".  In the Source box that appears, enter the list of items you want in the dropdown list in one of three ways:

1) Comma-separated list of items like "a,b,c" (no quotes)
2) An equal sign and a cell range that contains the items you want to show in the dropdown (like G1:G7 or Sheet2!A1:A12)
3) An equal sign and a named range like =MyImportantList (where MyImportantList is defined as something like G1:G7)

Another way not mentioned is to add a combobox to a userform, but I suspect that might be overkill for your stuff.
0
 

Author Comment

by:lifeactuary
ID: 39217183
Thanks  for the syggestions.

Although, as pointed out, it might be overkill, I'm gong to try the idea:of adding a combobox to the user form and see how that works. I'm leaning toward it becuse I don't like the idea of haveing a datavalidation box for each cell.

Will report back.
Thanks again.

Ed
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39217257
Ultimately, you have to get the data somewhere, and you can copy/paste data validation so it shows up without much effort....but....

I'm not sure if you know how to add a user form or combo (dropdown) box, so here are some guidelines:

* Start with Alt+F11 to launch the VBA window.
* On the Insert menu, select UserForm
* with the UserForm selected, from the toolbox (menu View | Toolbox if needed), drag the 4th item on the top row (next to the "ab|"
* with the combobox selected, in the Properties window (F4 to show if needed) go to the RowSource property and put in the desired range (e.g. A1:A10 or a named range) where the items for the list are stored.

with the form selected, Run (F5) to see how the list works.
After that, you'll probably want a trigger (maybe a button click) to launch the userform.

Let me know when you get there and I'll help you get it to interact with the sheet.
0
 

Author Comment

by:lifeactuary
ID: 39217355
Great info! Thanks.
Will try and get back to you.
Ed
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:lifeactuary
ID: 39305321
Sorry for delay in getting bak.

Solution works except that when I try to add an item, in this case, a textbox,. I get message:" Library not registered".

Tried adding what I thought might be appropriate libraries, but no sucess.

What do I do now.
Thanks
Ed
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39305566
I'll probably need more info to solve that one.
where is the error occurring?
-When you add an item from the toolbox?  Sounds like maybe your installation needs to be reset.
- in some of the code? which line?

maybe it makes sense to attach the workbook (minus important data) to see if others get the same problem.
0
 

Author Comment

by:lifeactuary
ID: 39305638
OK. Tried opening Excel as Administrator and that worked. Now wondering how to set things up so as to get EXcel to always run as Administrator!
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39306182
One way to "get EXcel to always run as Administrator" in windows 7 is to drag a shortcut to excel onto the desktop (or wherever needed) then right-click the shortcut icon and select the Shortcut tab. Click on the Advanced... button near the bottom.  The new window has a checkbox for "Run as Administrator".  (for some reason, mine is disabled, but that may work for you.)
0
 

Author Comment

by:lifeactuary
ID: 39306885
That worked! Thanks.
Ed
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 39313243
Glad that worked for you.  Let me know if you have any more issues/concerns before you close this.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now