Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel use drop down in form

Posted on 2013-06-02
12
Medium Priority
?
217 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 101

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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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.

916 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