Chat with us, powered by LiveChat

Front End Forms & Questionnaires

With Front-End Forms & Questionnaires, Excel does not just tick all the right but also provides the boxes to be ticked at the right time, in the right place and under the right conditions. At The Excel Experts we have considerable experience in building Form based systems.

Front-End Forms & Questionnaires are user friendly methods for reading and writing data and information. These include forms for ordering, marketing, managing contact lists, creating contracts and numerous other applications including questionnaires.

This where Excel stops being open to the user. Worksheet and workbook protection restrict what the user can do to what is needed and that alone. This is added to with Data Validation and Conditional Formatting so that there is no accidental input.

The overview of the system is one that ensures that all the data either entered or provided to be chosen is always consistent and accurate as required by the task in hand. In turn ensuring the output is exactly as required.

The current approach is not to use VBA’s antiquated Userform but to design fully customised forms on the Excel worksheet itself. This allows greater flexibility with two types of form controls available to the developer, including ActiveX.

Basic form controls are textboxes, list boxes, dropdown boxes, radio buttons, checkboxes and the like. However advanced controls can be from the likes of tab strips to another application window e.g. a web browser embedded into the Front-End form & questionnaire.

Demonstrating Excel shapes

Visual Interpretation in Front End Forms

The excel environment can be controlled to show only that which is need.

Excel comes with an extensive Shapes Collection in addition to all fonts, font symbols in all sizes. All of which can be custom formatted with a variety of fill and borders. While the use of shapes as symbols can aid in guiding the user, all shapes can be made ‘clickable’ and run their assigned macros. Also, shapes can act as layers and containers for text, pictures and other shapes enabling complex form construction. This in turn enables the other main benefit of using the worksheet as a form and that is the ability to exactly match the clients branding.

A good Front-End form & questionnaire should impart confidence to the user. It has become standard for us to work with a visual brief supplied by the client’s designers. Leaving it to us to copy an exact visual image using all that Excel offers and then add then add the required functionality.

A front end form built inside a shape that creates and edits another form.
A form built inside a shape that creates and edits another form. All Excel shell controls turned off so minimal Excel left showing.

The Mechanics behind the Front End Form

Excel enables the developer to build in the skills and knowledge required better and quicker than any other platform.

The mechanics behind the form can filter and calculate the data, before displaying only that required by the user at the exact point of application. Adapting both look and functionality to the exact requirements of the task in hand at each stage of the process.

The arguments applicable to the data is referred to as the ‘Logic Tree’, and this determines the input/output restrictions for each step. At each stage Excel creates the appropriate form format for the data required for each of the following steps to be entered.

The whole point of bespoke front-end forms & questionnaires is to delegate the task in hand with minimal user training. Where the whole process is designed to eliminate any possible error by the user and deliver exactly what is required.

Excel Front end Form with Excel shell showing
Form with Excel shell showing

Excel front end form without Excel shell showing
Same form without Excel shell showing

Location


At one extreme, an Excel form can be designed to work globally across an organization’s physical location including those of its clients where necessary. At the other an Excel form can be designed to just operate in a single location on a single off-line computer

With Excel’s ability to act as a ‘universal connecter’, the form can draw its data from, and write its data to, any other data source. See API Connections and SharePoint Services

Security Implementation In Front End Forms

Most projects that we have worked on include some form of restricted access and data security requirements. Yet again, Excel is equipped to meet the client’s exact requirements regarding this better and with greater efficiency.

Recent Application of Front End Form

We have noticed a recent trend with organisations that operating large databases. That is to use Excel’s connectivity to design bespoke front-end forms to work with their databases. In our opinion it is the industry finally growing up and recognizing that Excel ‘s versatility offers better value.

For the same reasons, we have been asked to provide Excel based questionnaires that impart knowledge and understanding. The end point being that the user can make the right informed choice without the need to talk with an expert. This obviously produces a saving that can be passed on to the client.

On a general note, where clients previously looked at Web Portals and Web Browser solutions the industry is beginning to realise that Excel is a web browser for data. The difference is the speed at which applications (all form based) can be built and delivered to the client.