MAKING EXCEL WORK FOR YOU

Heading

 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy


Use a VBA messagebox  to get user input...

Value

Meaning

0

A Formula

1

A Number

2

A Text String

4

A logical value (True or False)

8

 A cell reference, I.e a Range address.

16

 An error value, e.g. #N/A

64

 An array of values

Click to view sample code Click to view sample code for a messagebox

Return Values

1

OK

vbOK

2


vbCancel

3

Cancel

vbAbort

4

Retry

vbRetry

5

Ignore

vbIgnore

6

Yes

vbYes

7

No

vbNo

Type of Button(s)

0

VBOKOnly

OK button only

1

vbOKCancel

OK with Cancel buttons

2

vbAbortRetryIgnore

Abort, Retry and Ignore buttons

3

vbYesNoCancel

Yes, No and cancel buttons

4

vbYesNo

Yes and No buttons

5

vbRetryCancel

Retry with cancel buttons

This is a simple example to get a user's input with the inbuilt  InputBox. This useful where you only need a little information and do not need to create a UserForm.

InputBox Method

This method displays a dialog box for the user ito enter a response. An inputBox can be used to Return the information entered to a spreadsheet. You can define the InputBox by using these parameters:

expression.InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)


You can use the sum of the allowable values for Type e.g.for an input box that can accept both text and numbers, set Type to 1 + 2.

Notes: Use an InputBox to display a simple dialog box so that you can enter information to be used in a macro. The dialog box has an OK button and a Cancel button. If you choose the OK button, InputBox returns the value entered in the dialog box. If you click the Cancel button, InputBox returns False.

If Type is 0, InputBox returns the formula in the form of text, for example, "=2*PI()/360". If there are any references in the formula, they are returned as A1-style references. (Use ConvertFormula to convert between reference styles.)

If Type is 8, InputBox returns a Range object. You must use the Set statement to assign the result to a Range object, as shown in the following example.




Note: If you don't use the Set statement, the variable is set to the value in the range, rather than the Range object itself.

If you use the InputBox method to ask the user for a formula, you must use the FormulaLocal property to assign the formula to a Range object. The input formula will be in the user's language.

The InputBox method differs from the InputBox function in that it allows selective validation of the user's input, and it can be used with Microsoft Excel objects, error values, and formulas. Note that Application.InputBox calls the InputBox method; InputBox with no object qualifier calls the InputBox function.


Excel Messagebox (msgbox)

The MsgBox function in VBA displays a message in a window and waits until the user clicks a button on the message box. This is a useful function to pass information to the user.


A Message Box returns an integer value to indicate clicked button (Yes, No, Cancel, etc.):

You can specify the number and type of buttons for the Message Box (the default value for buttons is 0 , this will display OK button only):You can type either the number e.g. 4 and in this case


To show a MsgBox with the message "Hi", place a command button on your worksheet and simply add the following code line:

Msgbox “Hi”

You can make the message a little more dynamic by perhaps adding the date.

Msgbox “Hi,  today is ” & Date

Date is a VBA Function that returns the system date, the time would be returned by using Time. You could perhaps personalise it by adding the user’s name. Note the use of spaces to format the message correctly

Msgbox “Hi, ” & Environ(“username” & “  today is ” & Date

You can show a messagebox to display a value entered into a Cell of a WorkSheet.


Msgbox Sheet1.Cells(1,1).Value

The above example will display any text or number entered in A1 of Sheet1

You can split the message into lines by using vbNewLine, e.g.

Msgbox “Hi, ” & Environ(“username” & “  today is ” & Date & vbNewLine & Sheet1.Cells(1,1).Value

By default the above code examples will display the OK button to dismiss the messagebox. You can change the button(s) by using it’s Type, add a Title by setting out like so:

               MsgBox(prompt[, buttons] [, title] [, helpfile, context])



Click to view code for a messagebox with three buttons