Macro user interaction

I record a lot of macros to speed up some of my more repetitive tasks. The trouble is they don’t interact with the user. How do I set up my macros to interact with the user?

By Neale Blackwood, 1 Nov 09

A recorded macro can’t interact with the user. You have to amend the recorded macro (VBA) code to interact with the user. There are at least five ways to do this. These are in order of simplicity.

1. Input cells – use input cells in your sheet that the user can change before running the macro. The macro then refers to these cells to determine what to do.

2. Message Box – these are the standard Yes, No and Cancel dialog boxes that are seen throughout Windows.

3. Input Box – this displays a popup dialog that allows the user to enter data into a text field for the macro to use. Eg enter a date, or a name.

4. Excel Dialogs – you can use VBA code to display Excel’s built-in dialogs eg Open file dialog.

5. User Form – these are custom designed dialogs you can build in VBA that can contain controls to allow the user to easily make selections and enter data.

Each method has its advantages. Input cells require very little VBA code to use and don’t have to wait for the user to respond, but if the input value is missing, or wrong, then the macro may not work properly. Message Boxes are great for simple Yes / No answers, but won’t let the user input anything. The Input Box accepts text entries and requires extra VBA code to validate their entries. The User Forms are the most robust. Their only drawback is that they require a lot of VBA experience to use effectively.

First published: CPA AUSTRALIA Magazine INTHEBLACK December 2009 – Page 70 Excel Yourself

Extras

I have included examples of the first two methods. The assumption is that user has to decide if the file should be saved by the macro.
Input cells – the first example has a cell named Input in the sheet that contains Yes or No.
VBA code segments (these are code fragments only – not full macros)

Input cell example:

If [Input] = “Yes” Then

ActiveWorkbook.Save

End If

Using the square brackets [ ] around a range name is an easy way to use range names in VBA code.

Message Box example:

If MsgBox(“Save the file?”, vbYesNo, “Save?”) = vbYes Then

ActiveWorkbook.Save

End If

This dialog will display when the second macro fragment is run. The text that displays is set by the VBA code.

tip88-1