Solved

excel use drop down in form

Posted on 2013-06-02
12
177 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

919 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

17 Experts available now in Live!

Get 1:1 Help Now