Message Box Builder

The Message Box Builder generates code for Message Boxes and Input Boxes. It can be found in the UI menu:

Message Box

Message Boxes are used to prompt users. The default MessageBox (vbOKOnly), communicates information and simply asks the user to click ‘ok’.

msgbox "Complete"

Other MessageBox types require users to make a choice (Exs. OK / Cancel, Retry / Cancel):

The Preview area shows the currently configured MessageBox. Simply change the options and immediately the preview area will show the updated MessageBox and the Generated Code area will show the generated code:

A couple things of note:

You can define a default button by clicking the specific button in the “Buttons and Default Button” section. This is the button that will be pressed if the user clicks Enter when the MessageBox is shown. In the previous two images, the option “No” was selected as the default button.

The MessageBox outputs a value corresponding to the button that the user clicked. In the Generated Code box you can see a Select Case statement is used to handle the different options:

Select Case answer
   Case vbYes
   Case vbNo
   Case vbCancel
End Select

The procedure should perform different actions depending on if Yes, No, or Cancel is selected. To accomplish this, enter the desired actions into the Select Case statement:

Select Case answer
    Case vbYes
        range("a1").value = "yes"
    Case vbNo
        range("a1").value = "no"
    Case vbCancel
        Goto EndMacro
    End Select

Note: Defining actions is not required with a OKOnly MessageBox type as only one option is available.

Input Box Builder

InputBoxes ask users to enter a value. That value is assigned to a variable:

Dim answer As Double
answer = Application.InputBox("Interest Rate", "Enter Interest Rate", Type:=1)

The InputBox portion of the Builder works very similarly to the MessageBox portion. However, there are two options that we want to highlight.

Input Type

Input Type tells VBA what type of input to expect from the user. This allows VBA to determine if the inputted value is valid.

Note: To prevent errors, when working with input type=Cell Reference, Application.ScreenUpdating must be set to True: Application.ScreenUpdating = True

Default Value The default value option pre-populates the input area.

Start Automating Excel

Get AutoMacro