• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data entry forms *without* macros

D

DeepField

New Member
Our company has made available a Sharepoint Server where one can post files and other users can open and modify them (in Internet Explorer). The problem is that files that contain advanced (and potentially dangerous) content, such as external links and macros are not available that way and need to be downloaded in order to be opened.


We want to have a "Production Summary Dashboard" in which every plant manager enters his/her plant's monthly data (units produced, batches, man-hours), and the dashboard then shows its updated year-to-date performance.


The problem is that there are several plant managers that need to update the same data table, appending records at the end. There is always the risk of changing previous months data, entering duplicate records, or otherwise corrupting the existing information.


The question is: Is there a way of implementing a data entry form, with dropboxes to limit misspelling of text fields, but without any macros or VBA?. The Autoform feature is almost good, but one cannot restrict what the user writes on those fields that should limit to a list.


Thanks...
 
Check out Data Validation. You can use that to limit what a user can input to just a pre-determined list, or set rules (e.g., value less than 10000, a date, length of text, etc)

Check out Debra's introduction site to help you get started.

www.contextures.com/xlDataVal01.html
 
Yep, data validation is probably what you're after. Also check out using protect sheet (under the review tab) if you want to stop people from messing with cells you don't want them to change.
 
Thank you for your answers. However, I know what data validation is and how to use it. What I want is a data-entry form (so the user does not need to navigate to the bottom of a several-hundred-record table) with data validation (so the fields that should be limited to a list are forced to be) and with no macros (so the file can be published and used directly on the Sharepoint portal).
 
Hi, DeepField!

Sorry to ask this, but what should the user form (only form type available in Excel) to with the data entered on it? If you don't want any VBA code I'm afraid nothing.

Regards!
 
Excel has a "Form" button that one can use on top of a table, and when you click on it Excel brings up a form that has all the fields in the table, and that lets you navigate through its data and append new records to it. You can use it in a .xlsx (no macros) file, but it does not validate your data. I want to know if there is some way of tuning that form (or create a user-defined one) to add data-validation but without macros.


I am also considering Infopath forms, but I do not know that program at all and do not know whether it can interact with Excel files.
 
Good evening DeepFiel

You could add the form button to the QAT, do this via Options/ Quick Access Toolbar, if you have Data in the spread sheet the form will pick up the header names you have entered.

This link will take you to a post I started. http://chandoo.org/forums/topic/user-form-via-qat

There are reply’s from SirJB7 and 3000up Luke M on how to get the spread sheet open with the form displayed
 
Hi DeepField,

I'm afraid that just using the basic Form button within XL, you'll not be able to get the limits/contraints that you are wanting. =(
 
Hi, DeepField!

Sorry if wasn't fully clear when I wrote the only form type available in Excel. I tried to mean the only one which you can tweak, control, or validate input as you require.

Regards!
 
You must log in or register to reply here.
Back
Top