Excel 2010 Macro Message Boxes and Input Boxes
Create Message Boxes using Macros
The MsgBox function creates a message box that displays information and waits for the user to click a button before continuing. While InputBox only has OK and Cancel buttons, the MsgBox function lets you choose from several buttons configurations, including Yes, No, OK, and Cancel. You can also configure the prompt, the window title, and help files.
The code below brings up a prompt to find out whether the user wants to continue. A Select Case statement is then used to continue the program with the appropriate action.
myTitle = “Sample Message”
MyMsg = “Do you want to Continue?”
Response = MsgBox(myMsg, vbExclamation + vbYesNoCancel, myTitle)
Select Case Response
Case Is = vbYes
ActiveWorkbook.Close SaveChanges:=False
Case Is = vbNo
ActiveWorkbook.Close SaveChanges:=True
Case Is = vbCancel
Exit Sub
End Select
Create Input Boxes using Macros
The InputBox function is used to create a basic interface element that requests input from the user before the program continues. You can configure the prompt, the title for the window, a default value, the window position, and user help files. Only two buttons are provided: the OK and Cancel buttons. The returned value is a string.
The following code asks the user for the number of months to be averaged:
AveMos = InputBox(Prompt:=”Enter the number “ & _
“ of months to average”, Title:=”Enter Months”, _
Default:=”3”)