Microsoft Office Development and Consultancy

Excel

Step 1 - Understand the Controls

These controls are all ActiveX controls and will require you to add some VBA code to the necessary events.

 Design Mode - Switches to forms design mode, where you can edit or create a form by using the ActiveX tools on the Control Toolbox.

 Properties - Displays the properties window allowing you to change the properties of the control.

 View Code - Displays the Visual Basic Editor.

 Check Box - Allows the user to either select or deselect an option.

 Text Box - Allows you to display text and to allow the user to enter information.

 Command Button - Allows the user to perform a specific action by running a subroutine.

 Option Button - Allows the user to select from a list of possible choices. These are normally placed inside frames.

 List Box - Allows the user to select from a list of possible choices.

 Combo Box - Allows the user to either select an item from the drop-down list or to enter a different value into the text box at the top.

 Toggle Button - Allows the user to toggle between two states.

 Spin Button - Allows the user to select a value by clicking on two buttons either to increase or decrease its value.

 Scroll Bar - Allows the user to drag a button to change a value.

 Label - Allows you to display text to the user.

 Image - Allows you to display a graphic or picture.

 More Controls - Lets you add additional controls to your document.

Step 2 - Design Mode

There are actually two modes available, "Design Mode" and "Run Mode".

Design mode allows you to work with the control without actually firing any events.

This is the mode that allows you to change the size and location of your controls.

The controls can only be moved, resized or deleted when in Design Mode.

As soon as you select a control from the toolbar you will be automatically placed in Design Mode.

This button changes to Exit Design Mode when you are in design mode.

To trap any of the events the necessary Event Procedure must be placed in the Worksheet Code Module and not in a general Code module.

To add an Event to one of these controls, just double click the control while in Design mode.

You can add a different event by using the procedure drop-down box in the top right of the Code window

Step 3 - Run Mode

Every time you select a control with your mouse it assumes that you want to use the control.

Clicking on a control when in this mode will fire the corresponding events and any code associated with it will run.

Step 4 - Adding More Controls

Step 5 - Things to Remember

The following controls cannot be added to a worksheet, Tab Control, Multi Page, RefEdit.

You can size controls automatically to fit cells by holding down the Alt key as you adjust the size.

You cannot add these controls to a Chart Sheet.

When you add a control to a worksheet it actually sits in the drawing layer (as opposed to the Value or Formula layer).