Solved

Customize or Create an Excel Data Form

Posted on 2014-03-20
15
1,831 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 30

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 30

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
Turn your laptop into a mobile console!

The CV211 Laptop USB Console Adapter provides a direct Laptop-to-Computer connection for fast and easy remote desktop access with no software to install.

 

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 30

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 30

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 30

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 30

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 30

Accepted Solution

by:
gowflow earned 500 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 30

Expert Comment

by:gowflow
ID: 39959467
Your welcome.
gowflow
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

696 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