Quattro Pro Tutorial

Working with Form Controls

Quattro Pro allows you to include Windows-style form controls in your spreadsheet. Examples of form controls are sliders, buttons, combo boxes, and radio buttons that you can use to make using your spreadsheet easier. At first it might seem like form controls in spreadsheets are silly gimmicks, but then again spreadsheets can become very complicated and therefore difficult to use. Also, the person using your spreadsheet might not know anything about writing spreadsheet applications, entering data, saving files, etc. This is where form controls come in. They can help the user enter data, select parameters for calculations and even run macros. Form controls can also be useful in simple spreadsheets and since they are fairly easy to add and set up you may find them convenient and not-so-gimmicky features of your spreadsheet.

Only one exercise is given in this tutorial. It involves the use of a scroll bar control to change the parameters used in calculations done in an earlier tutorial. It should help get you started using this and other types of form controls.

Contents


Form Controls Available in Quattro Pro

Quattro Pro includes a number of the more common Windows controls and makes it easy to add them to your spreadsheet. All you do is select them from the list in the main menu - Insert|Form Control (see the below), edit their properties, and then immediately start using them.

The form controls menu
The form controls menu.

The following table summarizes the main characteristics of each of these controls:

Control Description
Push Button This control executes a macro when clicked.
Check Box This control puts a 0 if not checked, or a 1 if checked, in a destination cell. The state of this control can also be changed by typing a 0 or 1 in the destination cell.
Radio Button This controls is usually grouped with other radio buttons. If you place three radio buttons in a group box, for instance, then when you click one of them all others are set to off and an integer indicating which button was pressed is output to the destination cell. If a macro has been defined for these controls the macro will be executed. If none of them are pressed, the output value is 0. If the first button is pressed the output value is 1, for the second the output is 2, and so on.  The state of this control can also be changed by entering a value in the destination cell.
Group Box A group box is used to group several controls and can be used to create a Windows-style group box in your spreadsheet. One advantage of using group boxes is that when it is moved all of the components in it also move. Grouping controls can also affect how they work. For example, only one of the radio buttons in a group box can be in the "pressed" state at any one time.
List Box A list box displays the contents of a group of source cells and allow the user to scroll through the list an select one item. The item index (position in the list) is then placed in a destination cell. The item index is -1 if no selection has been made, and by editing the destination cell directly one can scroll to any item in the list.
Combo Box A combo box is very similar to a list box except instead of displaying all of the items in the list it displays only the selected item. When you click this control a partial list "drops down" to make it easier to select an item. As with the list box, the item index (position in the list) of the selection is placed in a destination cell and this destination cell can be edited directly.
Label Text This component lets you add text to the group box, for instance, adding a label to a control. Label text is not the same as text in a cell. Label text is "owned" by the group box.
Spin Control The spin control component lets the user select a integer value, such as the number of copies to print, by clicking the up or down button. To configure it you simply set the range (minimum and maximum) of values allowed and specify the cell to receive the result. Editing the destination cell directly will change the value in the spin control.
Vertical Scroll Bar When the tab in this scroll bar is moved its position, an integer, is placed in a destination cell. A macro can also be specified that will run when the position changes. The position can also be changed by editing the destination cell directly.

A simple example showing many of the above controls is in the file qpFormControlDemo.qpw. Load this spreadsheet and experiment with the controls.The figure below is a screen shot from that spreadsheet.

A demonstration of some form controls
An illustration of the types of form controls available and how the data are exchanged between these controls and the spreadsheet.
Back to top

Adding a Form Control to Your Spreadsheet

Adding a control to your application is simple. The basic procedure is:

  1. Select the component from the list given in the main menu under Insert|Form Control.
  2. Place it in the spreadsheet.
  3. Adjust its size and location.
  4. Edit its properties by right-clicking the control and filling in the information in the properties dialog.
  5. Test it.

Adding and implementing form controls requires a little extra effort and often the ability to write macros, but unless you are writing an elaborate application and are only using a few components we are only talking about a few minutes work per control. For example, if your spreadsheet already uses macros, adding a button to run these macros is a very simple procedure and improves the easy-of-use of your spreadsheet considerably. Here is how you might add such a button:

  1. Write the macro and give it a name.
  2. Place the button on your spreadsheet.
  3. Edit the button’s properties. Change the label text and specify the name of the macro to run. Make sure you enclose the name of the macro in {} brackets.
  4. Click the button and check the result after your macro has run.

The example in the figure above includes a button that runs a macro that prints the current sheet. The macro is also shown.

Back to top

Example

The figure below provides a god example of how one can use form controls to make a spreadsheet much easier to use. In this spreadsheet the speed of a bicycle is calculated from the bike's gearing, wheel diameter, and cadence (pedaling speed). Naturally all of these parameters are defined in cells, and one can easily change them by editing these cells. But then, typing numbers in for wheel diameter and other parameters might not be the most effective way to use this spreadsheet. On the other hand, using a scroll bar to be able to continuously vary the wheel diameter would work much better.  This is where the form controls come in, and two vertical scroll bars were placed next to the chart and configured so that they would change the wheel diameter and cadence.

A spreadsheet that utilizes form controls
A screen shot of a spreadsheet that uses vertical slider form controls. (update this figure)

Here's how it was done for the wheel diameter scroll bar. The same procedure was used to set up the cadence scroll bar.

  1. The scroll bar was placed in the spreadsheet and resized.
  2. A descriptions of the scroll bar's function was entered in a cell above the slider.
  3. The cell just above the scroll bar, cell H13, references the cell that contains the wheel diameter. This cell was on a different sheet but I didn't want to have to flip to that sheet to see what the diameter was.
  4. The scroll bar's properties dialog (see below) was opened by right-clicking the control and selecting "Selection Properties" (F12 is the equivalent shortcut).
  5. Three properties were changed. (The properties form is shown below.)
    1. The range was set to a value of 100. This defined the maximum wheel diameter.
    2. The page size was set to 12, which set the size of the slider's tab to about 1/8 the length of the slider.
    3. The address of the cell to receive the output, which as noted earlier is on another sheet, was set.
The properties dialog for the scroll bar form control
The scroll bar's properties dialog form.
Back to top

Hints and Suggestions

  • Use group boxes to keep your controls together and to group radio buttons.

  • Add each control, configure it, and test it before placing it in a group box. When all controls are ready you can create a group box large enough to hold all the controls and then you can simply drag each control to the group box.

  • Avoid resizing group boxes after you have placed other controls in it because doing so will resize these controls.

  • The values or state of each component can be changed by editing their destination cells directly.

  • Set the default values for each component by editing their destination cells directly. Use a "start-up macro" to reset the defaults when the spreadsheet application is loaded.

  • Use the "Object Order" item in the pop-up menu (right-click) to specify which control is in front of or behind another.
Back to top