Solved

excel use drop down in form

Posted on 2013-06-02
12
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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
 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

710 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