?
Solved

Customize or Create an Excel Data Form

Posted on 2014-03-20
15
Medium Priority
?
1,966 Views
Last Modified: 2014-03-27
I found this nifty little tool in Excel where I can view, edit and add data to a spreadsheet via a Data Form.  This is exactly what I was looking for, because my spreadsheet has several columns and I have to scroll back and forth when viewing or updating.  The only drawback is that the dataform is too small, the fields set to a default size and I cannot add validation.

Is there any way to customize this data form or a way to create one using a userform?
0
Comment
Question by:zepold
[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
  • 8
  • 7
15 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39942799
where is this 'nifty little tool' ? don't know what your talking about. Can you post it ?
gowlfow
0
 

Author Comment

by:zepold
ID: 39942828
In Excel, go to OPTIONS and select QUICK ACCESS TOOLBAR.  In the CHOOSE COMMANDS FROM field, select COMMANDS NOT IN THE RIBBON.  Scroll down the list until you find FORMS, select it and click on the ADD button.  That form option is now available on your Quick Access Toolbar.

Now, go to a spreadsheet that has column headers and click on any cell that has data.  Then go to your Quick Access Toolbar and click on the new FORM button.  The data form will pop up over your spreadsheet.

dl
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39942860
wow I learned something !!! :)

Nice

Now lets go back to what you want:
you say
The only drawback is that the dataform is too small, the fields set to a default size and I cannot add validation.


I say:
Please post a sample and tell me exactly what you want and I will get you my feedback on it as so far your post is way to general and vague !

gowflow
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:zepold
ID: 39943283
Here is a sample of my spreadsheet with a userform that I was working on.  It is no where near complete but it will give you an idea of what I was going for.  While searching for pointers and hints, I came across the Data Form mentioned earlier.  It did everything that I eventually wanted my user form to do.
Data-Form-Spreadsheet.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39943380
by the way, you said the dataform:
is too small, the fields set to a default size and I cannot add validation.

WRONG !!!!
whatever you set as validation on your columns is reflected in the dataform !!! try it before going further.


gowflow
0
 

Author Comment

by:zepold
ID: 39943513
Do not get my USERFORM confused with Excel's DATAFORM.  The userform that I included in the spreadsheet I attached is one the I created from scratch and was in the process of making a dataform.  That's when I ran into Excel's ready-made Data Form.  Which is perfect, except for those three issues.  Now, I didn't want to re-invent the wheel, so I was asking if Excel's Data Form can be modified.  If not, how can I get my userform to do what Excel's Data Form does?

dl
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39943786
Well I did not look at your dataform neither when I posted my previous comment nor now.

I do reiterate that if you do all your validation in the columns of data with Data Validation then the dataform that you pointed out will respect these validation and will abide by them.
Is this not enough for you  ? Why would oyu want to go to creating a Form ? what is the extra things oyu would want to achieve beside validation that you fear dataform will not provide ?
gowlfow
0
 

Author Comment

by:zepold
ID: 39943815
I see what you're talking about, now.  My apologies.  In the spreadsheet and in my userform, I have a drop-down list to pick from.  The Data Form does not show a drop down list.  The other issue, with the field size, fields like DESCRIPTION and NOTES/COMMENTS, can be several sentences long.  I would like to increase the height of those fields to be able to view the entire data in that cell.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39952650
Sorry for delay on this what are you missing in your userform ? what do you want to get it to work ? it is not clear.
gowflow
0
 

Author Comment

by:zepold
ID: 39953759
I want to create a custom userform that will do the same, or nearly the same, as the built-in data form.  I just want to be able to customize it to meet my needs.  Such as field size.  I want my userform to have a larger Description and Notes field in order to display all the text at once.

dl
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39955511
ok here it is. I took the liberty to adapt your form to the spirit of what you want. here are the changes performed:

1) I added 2 combo boxes replacing the text fields Priority and Status and linking all of location, Priority, Status to the sheet setting where they are automatically populated based on the data there. ie if you add items in this sheet it will automatically reflect in the corresponding comboboxes.

2) I changed your Add Project to New Project (here it will create a new instance of a new project that will be placed after the last existing project)

3) I introducted Delete project, Update Project and Clear Fields buttons.

4) Also introduced a Listbox that will automatically show all existing projects automatically updated. Simply click on any item in the listbox and it will pull the data in the corresponding controls (being textboxes or combobox)

5) When an item is selected in the listbox you can modify any field and by pressing on Update it will prompt you to Update the existing record (you can say no and it will not update it)

6) Delete has the same effect you can select an item and decide to delete it or not.

7) Any time you can press on Clear Fields and it will clear all fields.

Enjoy it
Let me know if it fits your needs.
gowflow
Data-Form-Spreadsheet-V01.xlsm
0
 

Author Comment

by:zepold
ID: 39956067
This is near perfect!!  The only glitch is that the UPDATE button does not effect the changes made.  After acknowledging, the fields go back to the original data.
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 39958289
You are absolutely correct and pls use this version and ignore the previous one altogether I had to perform several amendments as it was launching other sub while updating which was refreshing the current records and reinstating what was in the file instead of saving the modifications.

Had to do a major twist as despite using enableevents = false was still executing code as listbox is linked directly to column which when changed it triggers an autoupdate which loads the existing record again.

Pls try this version extensively.

gowflow
Data-Form-Spreadsheet-V02.xlsm
0
 

Author Closing Comment

by:zepold
ID: 39959148
Now, it's perfect!!  Thank you very much.

dl
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39959467
Your welcome.
gowflow
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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 describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
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…

752 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