Presentation Transcript
Using Message Boxes and Input Boxes in Excel VBA :www.bluepecan.co.uk Using Message Boxes and Input Boxes in Excel VBA Microsoft Office Training
Excel Training
Slide 2:www.bluepecan.co.uk 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 :www.bluepecan.co.uk 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 button.
Slide 4:www.bluepecan.co.uk 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 :www.bluepecan.co.uk 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 :www.bluepecan.co.uk 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
Slide 7:www.bluepecan.co.uk See this Excel training tutorial on the Blue Pecan website