Solved

Customize or Create an Excel Data Form

Posted on 2014-03-20
15
1,515 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
  • 8
  • 7
15 Comments
 
LVL 29

Expert Comment

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

Author Comment

by:zepold
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:zepold
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:zepold
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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
Comment Utility
Now, it's perfect!!  Thank you very much.

dl
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Your welcome.
gowflow
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a nutshell Dropbox is a cloud-based data storage service which synchronizes with data files on your computers. The Dropbox folk provide 2GB of free storage but if you need more you can sign up for a 50GB or 100GB subscription account. I pr…
Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
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 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…

762 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

6 Experts available now in Live!

Get 1:1 Help Now