This video focuses on the properties of a Form control button, and in particular the common problem of a macro button moving or re-sizing in a financial model.

The use of a customised form control button, commonly referred to as a macro button, can add another dimension to a financial model’s value to a company. It can be particularly useful if a model developer needs to create a template, which will be rolled out across numerous divisions or business units.

The adoption of customised VBA macros can improve the automation of certain time-consuming, manual tasks in a financial model. Further, a macro can also improve the accuracy and consistency of certain spreadsheet tasks.

Therefore, it is important to understand the various properties settings for an Excel form control button.

Strategize FM – Form control macro buttons.xlsm

Inserting a single button or multiple buttons

In some situations, financial model developers might wish to insert form controls or buttons into a spreadsheet, and then assign a different macro – in order to automate a certain repetitive process. 

The following video outlines three simple buttons or form control options.

Why use a Form control versus an ActiveX control?

The answer is simple – Excel for Mac. Although most financial model users are on a DOS computer, which uses MS Excel, there are a growing number of people using Excel for Mac. Excel for Mac does not support ActiveX Controls (sometimes referred to as OCX files), and are likely to generate one or more of the below error messages. 

• The application “unknown” has unexpectedly quit, because an error of type 3 occurred.
• Run-time error ‘57121’: Can’t exit design mode because Control ‘CheckBox1’ can not be created.
• Run-time error ‘32809’: Application-defined or object-defined error
• Run-time error ‘1004’: Cannot start the source application for this object. There may not be enough memory available

Why a macro button or form control jumps around, undocks or even disappears

Curiously Excel’s Form button can resize, undock or move around (and even disappear), if the model developer doesn’t specify the correct properties. Naturally this can impact the user ability of the financial model – especially if the macro button disappears or shrinks to an unmanageable size in the corner of the spreadsheets.

It is crucial to adhere to the correct Form Control properties, in order to prevent  this problem.

Form Control Properties

Here are two areas of the Form Control properties to consider:

Object Positioning
The following three options are contingent on the personal needs of the model developer. 

• Move and size with cells. To ensure the control remains with a cell if the cell moves, and to align its resize whenever the height or width of the cell is altered.
• Move but don’t size with cells. The form control or macro button will remain with a cell, when the cell moves in the worksheet. Note the button’s size will not be impacted by any change in the cell’s height or width.
• Don’t move or size with cells. This option will ensure the form control is mutually exclusive; in other words the control’s position and size will not be affected by changes to the cell’s position, height or width.

Print object
It is preferable to enable by ticking this check box; even if the form control button doesn’t need to be printed. 

Concluding remarks

The preference is to select the first Object Positioning option, “Move and size with cells”, which will guarantee the form control button flexes and moves with changes to the underlying cells.

To prevent the macro button from re-sizing, undocking or moving around, it is advisable to enable the “Print object” feature – even if the spreadsheet will not require printing.