Solved

Excel:  How to apply Drop-Down Lists associated with Fields in a Data Form automatically created from a Data Table

Posted on 2015-02-08
8
344 Views
Last Modified: 2016-02-11
Using:  Excel 2011 ver 14.4.7

I use a Data Table which has drop-down lists in many of the fields (columns).

When I automatically create (show) a Data Form in Excel 2011 from this Data Table , and try to use this form to create a new record, all of the correct fields are shown on the form but they are all blank user input fields.

How can I get the features which apply to the fields in the Data Table (for example a Drop-Down List), to get reflected in the Data Form?

Please see attached example.
Data-Form-No-DropDown.jpg
0
Comment
Question by:qeng
  • 3
  • 3
  • 2
8 Comments
 
LVL 11

Assisted Solution

by:Wilder1626
Wilder1626 earned 300 total points
ID: 40597142
Have a look at my drop down sample in attachment.

If you create a range for your drop down menu and call it dbase

You can use it for your drop down menu in your cell, and also for your ComboBox in your form.

 Name Manager
drop down sampleYou can do it this way by calling your name range.
Ex:
Me.ComboBox1.RowSource = "dbase"

Open in new window

Drop-down-list-on-VBA-form.xlsm
0
 
LVL 11

Assisted Solution

by:Wilder1626
Wilder1626 earned 300 total points
ID: 40597170
If you did not created a Dynamic drop down list, it would be a good thing to do also. So if you add items in your drop down list column, the drop down list will auto adjust, same for your ComboBox on your form.

You would then need to use below code if for example, your data base is in column A:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Open in new window


Dynamic drop down listDrop-down-list-on-VBA-form.xlsm
0
 

Author Comment

by:qeng
ID: 40597241
Thanks Wilder, this seems very promising.

However, I'm wondering if my problem isn't specifically related to Excel 2011 (which I have to use since that is the version used by the user for whom i'm developing this).

I can't use the Show Form function in your example spreadsheet in Excel 2011.  The spreadsheet has to be opened in read-only mode and the show form function (even with all options enabled) is inactive.

I can open it in Excel 2007 (on the Windows side of my Mac) and run the form no problem.

Also, all of the drop down lists in my Data Table are named ranges.  So, for example, the drop down list in the Field:  Date uses a named ranged called 'Date_List' and I make use of that named range when I create the drop-down list in the first cell of the field by inserting the list name '=Date_List' through the Data Validation command.

What doesn't seem to happen in Excel 2011 though is if I use from the ribbon:  Data > Form, in the form which shows up, none of the fields have drop down lists.  The form shows all the correct values for a particular record in the Data Table but unlike the cell from which this data originates (which has a drop down cell in it), none of the fields in the Data Form shows any dropdown lists.  They just show up as a cell with data in it.

This means that my user would be guessing when they are entering data via the Data Form as to what values are appropriate for that field, unlike if they were entering the same data directly into the Data Table if I copied a row, deleted the entries and then let the user visit each cell and use the dropdown lists.
0
 
LVL 11

Assisted Solution

by:Wilder1626
Wilder1626 earned 300 total points
ID: 40597277
Sorry, i don't have a Mac to test it, but it should not be to complicated to adjust.

Can you tell me if this macro file working on Excel 2011 for Mac? Please validate the drop down menu.
Drop-down-list-on-VBA-form-mac.xlsm
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 18

Assisted Solution

by:Simon
Simon earned 200 total points
ID: 40597792
I don't think that dropdowns are a feature of the builtin 'form' view of lists in Excel 2011 that appears when you use Data/Form... menu. The field entry is validated by the rule that you specify but no dropdown is shown.
Wilder showed you custom-made user-form in his post above, which is not the same thing.
0
 

Author Comment

by:qeng
ID: 40598082
Simon is correct (Wilder, again, thank you for the code).

What I was hoping for, short of setting some configuration parameter which would automatically bring the Data Table validation features like lists into the Data Form, was some way of starting with the Data Form which is automatically generated by Excel 2011 (similar to a 'View Code' function) and then just adding the validation features one by one, then saving the form.  This would have saved me having to custom build the entire form from scratch (I'm not familiar enough with VBA to do this yet but getting there).

Do either of you know if there is a way to see and save the Excel Data Form code to use as a starting point?  Or some other way around this?
0
 
LVL 18

Accepted Solution

by:
Simon earned 200 total points
ID: 40598170
>Do either of you know if there is a way to see and save the Excel Data Form code to use as a starting point?  Or some other way around this?

I am 99.999% sure that there is no way to get access to the Excel Data Form code, as it is built-in, rather than being part of an add-in. It also looks like it is a deprecated feature, as it no longer appears on the standard ribbons/toolbars of most recent versions.

I'd think your only option is to build a userform, or use another application (such as MS Access) for the data-entry.
0
 

Author Closing Comment

by:qeng
ID: 40602869
Thanks for staying with me on this guys.  I will try building the user form.

Do either of you know if I can re-use part of the user form VBA code which Excel 2011 automatically generates with the Data > Form command (it would save a lot of work from having to create the entire form from scratch).  I would just need to add the drop-down list capabilities to that code.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
adobe premier render 10 36
mac maintenance 6 31
addressing a subform frim a subform inside it 8 17
Filling Blank Cells 14 8
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.

948 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

22 Experts available now in Live!

Get 1:1 Help Now