Tag Archives: form

Mastering the VBA MsgBox

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.75 out of 5)

Today shortly on how to master the Excel MsgBox. Although, the MessageBox is a common control used in most Window applications many VBA developers underestimate its usage – often making custom UserForms for tasks where the Excel MsgBox could just as well do the trick. An example? User Interaction – when user input is needed many VBA devs create custom UserForms not knowing that the MsgBox can return a Yes/No, Retry/Cancel, Ok/Cancel response when configured properly.

Example Excel MsgBox question

This example will produce a simple Excel MsgBox question with Yes/No buttons:

If MsgBox("Do you agree?", vbYesNo, "Question") = vbYes Then
    'If Yes...put some code here
    'If No...do this
End If

Excel MsgBox: Creating a question
Excel MsgBox: Creating a question

Easy right? Yet this is just a sample of what you can do with the MsgBox. See the link above to learn more on how to efficiently utilize the Excel MsgBox in your VBA projects!

Common uses for the Excel MsgBox

The Excel MsgBox can be used in multiple different ways. See some valuable mentions below:

  • Notification – show the user a pop-up with an information or notification message e.g. code has finished executing
  • Error handling – show the user an error pop-up message informing that a critical error has occurred. You can add a Retry button to ask if the user wants to retry running the code
  • Question – show the user a pop-up with a simple question on how to proceed with running the code e.g. do you want to open a file?, do you want to close the workbook?

MsgBox: Messages, Notifications, Questions

The common VBA message box can be well used to interact with users to show messages (information, errors, notifications etc.) or just as well ask simple questions (Ok/Cancel, Retry/Cancel). Let us start by analyzing the parameters of the MsgBox function.

Function: MsgBox

  • Prompt – the message you want to show in the message box
  • Buttons – Optional. One of the following set of buttons below. You can sum them up to get different kinds of message boxes like this vbRetryCancel + vbCritical :
    • vbOKCancel – Display OK and Cancel buttons
    • vbAbortRetryIgnore – Display Abort, Retry, and Ignore buttons
    • vbYesNoCancel – Display Yes, No, and Cancel buttons
    • vbYesNo – Display Yes and No buttons
    • vbRetryCancel – Display Retry and Cancel buttons
    • vbCritical – Display Critical Message icon
    • vbQuestion – Display Warning Query icon
    • vbExclamation – Display Warning Message icon
    • vbInformation – Display Information Message icon
    • vbDefaultButton1 – First button is default
    • vbDefaultButton2 – Second button is default
    • vbDefaultButton3 – Third button is default
    • vbDefaultButton4 – Fourth button is default
    • vbApplicationModal – Application modal; the user must respond to the message box before continuing work in the current application
    • vbSystemModal – System modal; all applications are suspended until the user responds to the message box
    • vbMsgBoxHelpButton – Adds Help button to the message box
    • vbMsgBoxSetForeground – Specifies the message box window as the foreground window
    • vbMsgBoxRight – Text is right aligned
    • vbMsgBoxRtlReading – Specifies text should appear as right-to-left reading on Hebrew and Arabic systems
  • Title – Optional. The title of the Message Box
  • Help – Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
  • Context – Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.

Now some examples:

Retry with Critical Error Icon

Call MsgBox("Try to run the VBA code again?", vbRetryCancel + vbCritical, "Error")
Excel VBA Tutorial: MsgBox example: Critical Error and Retry
MsgBox example: Critical Error and Retry

Align message and Question Icon

Call MsgBox("Align this to the right hand side", vbQuestion + vbMsgBoxRight, "Some Message")
Excel VBA Tutorial: MsgBox example: Information and align text
MsgBox example: Information and align text

User interaction with the MsgBox
The message box is a great tool for asking simple Yes/No questions within your VBA code. See some examples below:

If MsgBox("Do you agree?", vbYesNo, "Question") = vbYes Then
    'If Yes...put some code here
    'If No...then do this
End If

This will show the following question and pause the code until the user clicks on one of the buttons:

MsgBox example: Yes/No question
MsgBox example: Yes/No question

Similarly you can create a retry question which is convenient for error handling:

  On Error Goto ErrorHandl:
  'Some code here..
  If MsgBox("Retry?", vbYesNo, "Question") = vbRetry Then
     Goto Retry  
  End If

Let me know what you think!

Use Form Controls to make Dynamic Excel Charts (no VBA)

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)

Dynamic Excel charts are so much appealing to users and more pleasant in interaction. Fortunately Excel has PivotCharts and Slicers. But what if you want to have more control over your charts and use other controls such as Dropdowns, ScrollBars and Radiobuttons? Today I will show you how to easily use Form Controls to add interactivity to your Excel Charts without any VBA nor PivotTables/PivotCharts.

Form Controls

What are Form Controls? Form Controls can be accessed from the Developer ribbon in Excel (File->Options->Customize Ribbon). They are simple controls that can be dragged-and-dropped onto your Worksheets and easily configured.

Excel Form Controls
Excel Form Controls

What makes Form Controls special? They are configurable and can be linked to cells in your Excel Workbook. Look below for an example of how the ScrollBar properties look like. Notice especially the Cell Link property which will output the current value of the Form Control (in this case the scroll value). This opens a whole lot of different possibilities to use Form Controls in calculations/charts etc.
Example Form Control Properties
Example Form Control Properties

Dynamic Excel Charts with a ScrollBar

Let’s consider a first example with a ScrollBar.
On the left there is a simple table with 3 columns:

  • Company – name of a company
  • No. of employees – number of employees in the company
  • Total – the number to be presented in the chart cut off by the ScrollBar (only showing values above)

Notice that the Total formula take the value from the ScrollBar to set the current cut-off. By scrolling the ScrollBar notice that the formulas will automatically recalculate and the chart will refresh to show only values above the cut-off. Neat huh?

Excel Dynamic Chart with ScrollBar
Excel Dynamic Chart with ScrollBar

I tend to usually overlay the Form Controls over the chart itself so it is more obvious for the user that the chart is dynamic.

For Excel 2013 users – you can also use timeline scrollbars natively in PivotTables/Charts (available on the Analyze ribbon tab).

Dynamic Excel Charts with a Dropdown

Let’s now consider a different example using a Dropdown form control.
Again on the left side notice the data source table. I have simply split the employee column into two sections: IT employees and non-IT employees.

We will also create a dropdown with 3 options:

  • IT – sum only IT employees
  • Non-IT – sum only non-IT employees
  • All – sum all employees

Having these three categories we can link them to the Dropdown control so they appear when click on the bottom arrow. Simple right? Now let’s link the H2 cell with the Dropdown control value and we can now see the changes in the selection of the Dropdown. Notice that the Total cell formula with show different value depending on the selection in the Dropdown. Easy right?

Example Dropdown Form Control
Example Dropdown Form Control

Again I would recommend overlaying the chart with the Dropdown to make it seem like a natural part of the Excel Chart.

Dynamic Excel Charts with a Radiobuttons

Radiobuttons work similarly as Dropdowns with the difference that you have to group them using the GroupBox control to achieve mutual exclusion. See the example below:

Example FormControls Radiobutton
Example FormControls Radiobutton

Next steps

Excel Google Charts Tool – Learn how to add cool Google Charts to your Excel Workbook