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.
However, there is one pitful to avoid when inserting form control buttons.
In this simple Cash Payments input spreadsheet, we have two macro buttons –
one to hide empty rows and another to unhide hidden rows.
Let’s now close this spreadsheet.
And re-open the model.
We can immediately see there is a problem with the two form control buttons.
The first button has shrunk and is unreadable to model users.
Whilst the second button has in essence disappeared from view.
In this example, both macro buttons have required a manual resizing, in order to be workable in this spreadsheet.
Clearly this is not ideal.
Further, there are a number of reasons why it is preferable to use Form controls over ActiveX
The main reason is Excel for Mac.
For Excel for Mac does not support Active X controls.
It can trigger one of the following error messages.
As this Cash Payments example illustrates,
model developers must apply Form Control buttons in the right fashion.
We will now investigate Form Control – Properties, to ensure the correct implementation of a macro button
in a financial model.
First there are three options in Object Positioning:
Move and size with cells
Move but don’t size with cells
and Don’t move or size with cells.
This first statement confirms how a button will flex, and move with cells under the first option
The second option will enable the button to move with cells – but not to resize.
And finally, the third option will not resize nor permit the form control to move with the cells.
We will now investigate, in depth, how the three Object Positioning properties impact how a Form Control button functions.
As well as the impact of the enablement or non-enablement of the Print Object feature.
We are now starting to insert two Form Controls for each Object Positioning option – as illustrated.
One with the Print Object enabled and not enabled.
Each option corresponds to a simple macro – which will be assigned to the macro button.
By altering the Object Positioning properties, this video will demonstrate
how the properties will impact the Form Control buttons attributes
Please remember the three macros below are being used for illustrative purposes only.
We are focusing on the properties of a Form control button,
and in particular the common problem of a macro button moving or resizing in a financial model.
Once we are completed, we will be able to fully comprehend 0:05:28.066,0:05:28.000
how a macro button's properties will dictate how the form control functions.
Firstly, we are going to increase the row height of the Form control buttons.
Macro 1 button resizes and moves with the cells.
Macro 2 button moves with the cells but won’t resize.
Whilst Macro 3 will neither move nor resize with the cells.
Additionally in column K, we will resize the column width,
which further demonstrates how only Object Positioning property 1 – move and size with cells will fully adjust. 0:06:17.066,0:06:17.033
Move and size with cells will fully adjust.
Let's now save and close this spreadsheet.
We will immediately re-open the financial model with the Form control buttons.
Straight away we can see the macro buttons in column I are fine.
This is thanks to the enablement of the Print Object option –
which safeguards a form control button from resizing or undocking.
However it is the macro buttons in column K,
which tell a different story and this is due to the Print Object option.
As we can see this requires manual rework of the Form control buttons,
and is also a source of frustration for the spreadsheet users.
For illustrative purposes, we will quickly use
these macros to change some of the descriptive features of this financial model.
On conclusion and returning to the original Cash Payments input sheet.
We know now it is preferable to select the Move and size with cells – Object Positioning option.
More importantly, it is best to tick the Print Object feature – to safeguard our Form Control buttons from resizing or undocking.