Free Excel Training - Using Message and Input Boxes in Excel VBA

Microsoft Office Training > Excel Training > Free Computer Training > Using Message and Input Boxes in Excel VBA

Please feel free to use this content on your web site, but please include the links at the end

Using Message and Input Boxes in Excel VBA

If you want to make your macros interactive then one way of doing this is to introduce message boxes and input boxes.  These allow you to display messages that require a response or ask the user to enter values before the macro can proceed any further.

Simple greeting using a message box

Create an empty workbook and the switch to the Visual Basic Editor (VBE) by clicking Tools > Macro > Visual Basic Editor or by using the shortcut key ALT F11.  If you are using Excel 2007 click on the Developer ribbon and then click on the Visual Basic Editor button.

Once in the VBE environment you will need to create a module to hold your function.  Click Insert > Module In the code window enter the following procedure

Sub Greeting()
MsgBox "Welcome to my spreadsheet"
End Sub

Press F5 to run the macro

Passing a variable to a message box

In the same procedure as above we will create an input box variable called MyName and declare the variable as a string. We will then concatenate the message “Welcome to my spreadsheet” with the value in the MyName variable.  This joins the message in the message box with a value the user enters in an input box.

Sub Greeting()
Dim MyName As String
MyName = InputBox("What's your name?")
MsgBox "Hi There " & MyName & "."
End Sub

Press F5 to run the macro

Adding more information to a message box

Now we are going to use Date, Time and CurDir functions to add more information to our message box.  Adjust your code as shown below.

Sub Greeting()
Dim MyName As String
MyName = InputBox("What's your name?")
MsgBox "Hi There " & MyName & ". Today's date is " & Date & " and the time is " & Time & ". This file is saved in " & CurDir & "."
End Sub