Solved

Customize or Create an Excel Data Form

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

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
 

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 29

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 29

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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 29

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 29

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 29

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 29

Expert Comment

by:gowflow
ID: 39959467
Your welcome.
gowflow
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Photo Albums in PowerPoint Photo Albums are a very useful tool in PowerPoint and allow you quickly add a large number of images. The images can be formatted in a variety of ways so that you are able to create a professional looking presentation v…
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:
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

910 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

20 Experts available now in Live!

Get 1:1 Help Now