Solved

excel use drop down in form

Posted on 2013-06-02
12
208 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

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.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

624 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