MsgBox & InputBox

These can be used to display information to the user or ask the user to input information.


MsgBox Function

This is a built in dialog box that can be used to display a message to the user and wait for a response.
This function is extremely useful and is often very handy when you are debugging.
This function returns a value which indicates what choice the user made.
This function can be inserted anywhere in your code to pause the program and display a message to the user.


InputBox Function

This is a VBA built in dialog box that can be used to prompt the user for information.
This type of message box displays a message and waits for the user to respond by pressing a button.
This function always returns a String so you often have to convert the value to its correct datatype.
Even if a numerical value is entered it will be returned as a string.


 

If you press Cancel (or OK without typing anything) a zero-length string will be returned.
It is always a good idea to test the result before proceeding to this situation can be handled.


Excel - Application.InputBox

Excel also has its own InputBox function which can cause a lot of confusion.
This inputbox is very similar to the VBA one, the only difference is that with the Excel one you can specify the datatype that is returned.
For more information, please refer to the Application.InputBox page in Excel section.


Important

Never display a message when screenupdating is turned off. Always toggle either side of the processing.
Put all the user messages and prompts into a dedicated module (modMessages).
Always display a message box (or prompt) when a macro has finished.
Add all messages and prompts to the log file, very useful when debugging.


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

Next