Working with Excel VBA Message Boxes
Tell the user what is going on and build their confidence in your spreadsheet
A true story…
We are currently working on a spreadsheet development project here in the UK. The project involves updating a ‘legacy’ spreadsheet – one that has been around for a while. It’s a typical situation in which we might intervene: the file has bits and pieces ‘bolted on’, and the person who was in charge of it has left the business. It’s causing anxiety.
The spreadsheet contains various buttons and macros that run on workbook events including opening the workbook, some of these macros are very powerful. Here is the thing: nobody actually knows what happens when the buttons are pressed. People are aware that some code runs, but the consequences of the macros are mixed at best: some reports are created but, elsewhere, inputs disappear and formats changed for no apparent reason. The client team is frustrated.
What is missing here is helpful user interaction. The applications we develop must communicate with users to build their confidence in the file. One example is asking for permission to run a macro, and making sure the the user is aware of any adverse consequences such as losing pre-existing data; another example is notifying the user that a macro has run, and providing some helpful summary information from the analyses generated. Both are examples of a critical success factor in spreadsheet development: helpful user interaction. So, how do we use Excel VBA message boxes to create helpful user interaction?
This video series teaches you the basics of Excel VBA messages boxes. With the building blocks provided, you will be able to create powerful, dynamic message boxes that provide the user with the information they need, when they need it. First, we cover a simple message box. Then, we look at two possible applications of Excel VBA message boxes: first, to seek permission from the user to run a macro; second, to report back to the user after a macro is run. So, let’s get into it!
Video 1 – Simple Excel VBA Msgbox
In the first video, we look at how to create a simple message box. From the outset we deploy powerful coding concepts – we use activecell.value, for example, to make the message box display what is in the selected cell; this lends the message box a dynamic quality – it responds to different situations and provides appropriate feedback. We will harness this dynamic quality later. We also look at how to change the title of the message box from ‘Microsoft Excel’ – we don’t want Bill Gates taking all the credit!
Video 2 – Excel VBA Message Box for Confirmation
How can you (re-) build user confidence in your spreadsheet? One way is to ask the user to ‘commit’ to decisions using a confirmation message box before running code. We are all familiar with this idea from online forms and pieces of software – we are told what will happen, and given the opportunity to proceed, or to reverse the decision. This is particularly important in the spreadsheet environment where users may not be used to macros doing work for them. Correctly implemented, this kind of feature can build user confidence and supports user interaction – strong user interaction is one of the features of professional Excel applications. The important mechanism here is a ‘Yes / No’ message box combined with a conditional statement to take the code in one of two directions. Cool!