Leading the way in Microsoft Office Development

Step 1 - Formatting Worksheet Controls

There are various ways to change the properties and formatting of worksheet controls.

1) Using the Properties window (only available for the Control Toolbox Controls).

2) Using the (Format > Control) dialog box.

The (Format > Control) dialog box is applicable to any object that is placed on a worksheet in the drawing layer.

This included graphics, autoshapes and wordart.

The most useful tab is the "Properties" tab as this determines if the object is moved and resized when column widths and row heights are adjusted.

You cannot use the same method as you would use in the Visual Basic Editor.

You have to use the Align and Distribute buttons on the drawing toolbar since all worksheet controls as considered as graphical objects.

You can make several controls the same height or width by using the Ctrl and Shift buttons and then changing the height or width properties in the Properties window.

Step 2 - Size tab

Height - Enter the height you want for the selected object. The Height and Width settings are always the dimensions of an un-rotated object. Alternatively, you can adjust the height as a percentage of the original size by entering a percentage in the Height box under Scale. If the Lock aspect ratio check box is selected, the Height and Width settings change in relation to one another.

Width - Enter the width you want for the selected object. The Height and Width settings are always the dimensions of an un-rotated object. Alternatively, you can adjust the width as a percentage of the original size by entering a percentage in the Width box under Scale. If the Lock aspect ratio check box is selected, the Height and Width settings change in relation to one another.

Lock aspect ratio - If checked, the Height and Width settings change in relation to one another.

Relative to original picture size - Calculates the height and width percentages under Scale, based on the original size of the picture. This option is available only when the selected object is a picture. In PowerPoint, if you select this check box, click the resolution of your slide show monitor in the Resolution box.

Step 3 - Protection tab

Worksheet controls are very badly protected against accidental changes.

Locking any controls will not have any effect unless the worksheet is protected.

It is always worth clearing this box for all the controls and then protecting the whole worksheet when you have finished.

Step 4 - Properties tab

These are very important as they will determine how your control behaves when changes to the worksheet are made, i.e. the widths of rows and columns are adjusted.

Move and size with cells - For a graphic object on a chart, ensures that the object will move with and resize with the chart. For a graphic object on a worksheet, ensures that the object will move with and resize with its underlying cells. Use this option if you want to sort graphic objects with the underlying cells. The objects can be no taller than the row or no wider than the column you want to sort.

Move but don't size with cells - Ensures that a graphic object will move, but not resize, with its underlying cells.

Don't move or size with cells - Maintains the position and size of a graphic object on your worksheet.

Print object - Prints selected graphic objects when you print a worksheet. All objects will be printed by default.

Step 5 - Things to Remember

All worksheet controls can be moved and sized independently of row and column restrictions.