One of the most common mistakes in VBA is using incorrect forms/dialogs for usually simple tasks. The VBA UserForm is one of the most exploited VBA modules I have ever seen (as opposed to VBA Class modules which are underutilized ;)). VBA developers and Excel users seem to think often that every dialog box and form needs to have that personal touch. The effect is often the opposite – with users being confused or experiencing errors with custom UserForms without proper error handling.
Today I want to make it easier for all those VBA users out there.
The form/dialog decision tree
Use the guide below when making your decision on whether you need that UserForm or if you are using that Messsage Box or InputBox in the right case:
VBA Message Box is
When to use Message Boxes?
Use the VBA MsgBox in the following situations:
- Short message / information / warning – you want to present the user with a simple message be it an error, message or a warning
- Yes or No decisions – ask the user if he/she agrees to your questions/ask or if the macro is to proceed etc.
Don’t use the VBA MsgBox in the following situations:
- Long text messages – when wanting to display long messages or text
When to use the InputBox?
Use the VBA InputBox in the following situations:
- Simple single variable input – you want the user to input a simple variable such as age, length etc.
Don’t use the VBA InputBox in the following situations:
- Yes or No / Boolean variables – don’t ask the user to type in yes or no, 1 or 0 or similar boolean variables. Use a MsgBox instead.
- Enumerations – don’t ask the user to input an item from a discrete list of values. Use a UserForm and a dropdown or listbox instead
When to use the UserForm?
Use the VBA UserForm in the following situations:
- Complex input / select / interaction – use the UserForm when other options fail and simply a MsgBox or InputBox won’t do the trick
Don’t use the VBA UserForm in the following situations:
- Simple single variable input – need the user to input an age or length value? Why not use the InputBox instead?
- Yes or No / Boolean variables – use the MsgBox instead
Agree or disagree? Let me know in your comments!