BET: Microsoft Excel Macros - The Finishing Touches

Microsoft Office Development and Consultancy

Home

Excel

VBA

Excel > Macros > The Finishing Touches

< Previous | Next >

Adding Shortcut keys and Descriptions to your Macros

	This does not have to be done when you record the macro and can easily be done afterwards.
	You can add either a Shortcut Key or a Description to your macro by selecting (Tools > Macro > Macros) and selecting the "Options" button at the bottom.
	If you add a description before recording a macro then this description will appear as a comment on the first few lines of the recorded VBA code.
	Remember that any key combinations you assign to macros will take precedence over any built-in shortcut keys.
	Shortcut keys are case sensitive and you can use just the Ctrl key or a combination of both the Ctrl key and Shift key.

Public vs Private

	All procedures and Public by default.
	To prevent a macro or procedure from appearing in the (Tools > Macro > Macros) dialog box you can change the scope from Public to Private.
	It is always a good habit to explicitly state whether public or private to avoid any confusion.

Using the Status bar

	If you switch the ScreenUpdating off to prevent the screen from flickering it is worth using the Status bar to keep the user informed about the current status of the macro.
	If your macro takes a bit of time to complete using the status bar will ensure that the user does not think that the macro has crashed or is not responding.

1
2
3
4
5
6
7
8
9
10

' Make sure that the status bar is visible
Application.DisplayStatusbar = True

' Enter the message to be displayed
Application.Statusbar = "Please wait ….."

' **** Add Your Code Here ****

' When the macro has finished reset the status bar
Application.Statusbar = False

If you change the status bar to display a message to the user - make sure you reset it.

Using Worksheet Controls

An alternative to using custom dialog boxes is to actually place the controls directly onto the worksheet.

	When controls are placed directly onto a worksheet they are known as Worksheet Controls or Form Controls.
	A worksheet control is basically a graphical object that enables the user to enter information, perform an action or select a value.
	For more details please refer to the Worksheet Controls section.

Running your Macros

	An alternative to using a command button might be to run a macro by clicking a cell.
	Highlight the cell, hold down the Shift key and press (Edit > Copy Picture) and select as shown on screen.
	Hold down the Shift and press (Edit > Paste Picture). Now right mouse click and assign your macro.

< Previous | Top | Next >

Microsoft Office Development and Consultancy

Excel

Adding Shortcut keys and Descriptions to your Macros

This does not have to be done when you record the macro and can easily be done afterwards.

You can add either a Shortcut Key or a Description to your macro by selecting (Tools > Macro > Macros) and selecting the "Options" button at the bottom.

If you add a description before recording a macro then this description will appear as a comment on the first few lines of the recorded VBA code.

Remember that any key combinations you assign to macros will take precedence over any built-in shortcut keys.

Shortcut keys are case sensitive and you can use just the Ctrl key or a combination of both the Ctrl key and Shift key.

Public vs Private

All procedures and Public by default.

To prevent a macro or procedure from appearing in the (Tools > Macro > Macros) dialog box you can change the scope from Public to Private.

It is always a good habit to explicitly state whether public or private to avoid any confusion.

Using the Status bar

If you switch the ScreenUpdating off to prevent the screen from flickering it is worth using the Status bar to keep the user informed about the current status of the macro.

If your macro takes a bit of time to complete using the status bar will ensure that the user does not think that the macro has crashed or is not responding.

If you change the status bar to display a message to the user - make sure you reset it.

Using Worksheet Controls

An alternative to using custom dialog boxes is to actually place the controls directly onto the worksheet.

When controls are placed directly onto a worksheet they are known as Worksheet Controls or Form Controls.

A worksheet control is basically a graphical object that enables the user to enter information, perform an action or select a value.

For more details please refer to the Worksheet Controls section.

Running your Macros

An alternative to using a command button might be to run a macro by clicking a cell.

Highlight the cell, hold down the Shift key and press (Edit > Copy Picture) and select as shown on screen.

Hold down the Shift and press (Edit > Paste Picture). Now right mouse click and assign your macro.