98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
Using The Excel Forms Toolbar To Spruce Up A Workbook
Tue 11th May 2010
There are several ways to give a simple workbook a professional look and feel. If you want people to take your work seriously then it is important that data is presented not only with the use of appropriate charts, but also navigation and validation. There are several reasons for this. The first is when creating a worksheet where users are required to enter data; the data must be protected from user error. In order to avoid human error for example, you could create a very simple drop down list with values the designer can set. In this way nobody can misspell an entry and they can quickly and easily see what is required. If for example a sales spreadsheet required a country field and users were able to enter the country manually, they could misspell the country name. If a chart was created on the data to show sales per country, one country may have several pie slices, one for each spelling. The validation prevents this while saving the user time in having to manually enter the data.
As well as validation there are objects useful for navigation. When delivering a large workbook containing many sheets it can be cumbersome for a user to search through the tabs for what they require. As an example, picture a worksheet filled with sales data over an entire fiscal year. Each month has its own tab along with several tabs of summary information including charts based on the monthly and annual figures. There are therefore over fifteen tabs of data. If the user opens the workbook and wants to find an entry on the August tab they have to begin by moving through all the tabs to find August. If the designer of the worksheet has considered how the user can navigate through tabs more simply then a menu tab can be included with navigation buttons and macros placed to ensure a user-friendly workbook.
Always include an extra front tab on any large workbook. Record one macro per tab where you start on the menu tab and go to the month tab. On the first tab it is then possible to use the form buttons with the macros attached. This essentially gives you a menu page with navigation buttons so that a user can easily switch to the tabs they require without the need to search. It is even possible to turn off the tabs so the user does not see them. Using the same method you can create a button on each tab that will return the user to the main menu.
The form toolbar allows the designer to create these useful and professional looking objects very easily. When selecting the chosen form object it can simply be drawn onto the worksheet where it is required and the size adjusted as appropriate. Double clicking on the object brings up the properties for the object. These properties are organised into tabs. This allows the designer to set the data or attach a macro. To access the format controls on any existing object, right click it and double click on the border.
The more advanced user can drill down into these form objects even further by examining the VBA code behind them. This style of a simple design interface for the novice designer with the ability to directly alter the code for the more advanced user covers all bases. It gives anyone the ability and freedom to improve and upgrade their worksheets.
Author is a freelance copywriter. For more information on excel computer training, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-891-using-excel-forms-toolbar-spruce-up-workbook.html
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsAdarga
People Ops Georgia Traher Excel Intermediate Excellent course, inspired to do the advanced training. I would actually say the day could be longer! Ulster University
Martha Snookes Project Introduction Great training, thanks Samer for taking us through everything. I will be requesting an Intermediate training session through our Learning & Development team so I can take a deeper dive into the power of this software. Thanks. Crown Prosecution Service
Area Finance Manager Nathan Smith Improving Memory with Mind Mapping Well run course with a very experienced trainer. I would highly recommend this course. |
PUBLICATION GUIDELINES