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
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!