Tag Archives: UserForm

VBA MsgBox vs InputBox vs UserForm

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

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

Decision Tree: MsgBox vs InputBox vs UserForm
Decision Tree: MsgBox vs InputBox vs UserForm

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!

Animated VBA Progress Bar for Excel and Access

1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 4.14 out of 5)
Loading...

A VBA Progress Bar can be used to manage your users anxiety about the execution time of your VBA Macro. Sometimes there are very large and complex solutions built in Excel (which is a mistake mind you), where calculations or macro executions can take minutes or even hours. This causes many issues, especially for the end users who usually do not know how long processing the calculations/macros will take. In such cases it is important to notify the end users of the progress of your macros/calculations so they can switch to other activities. This is where the VBA Progress bar can aid you.
VBA Progress Bar example
For one of my older projects I needed a VBA Progress Bar that would show:

  1. The current progress of the computations
  2. How much execution time was left (estimation)

Users especially wanted to know how much execution time was left – whether they should grab a coffee or stay and wait for the macro to finish. Calculating this is best done by approximating the time it took to run

Animated VBA Progress Bar Example

The easiest approach to animating an Excel is to create a simple VBA UserForm with the use of a label control which width you can manipulate to show the current progress. Easy and straightforward.

The result:

vba progress bar
Automated VBA Progress Bar

Create your VBA Progress Bar

Below find a quick tutorial of how to create your own VBA Progress Bar User Form!

Create a customer User Form

First you need to create a customer VBA User Form. Click on the link in case you want a tutorial on how to create these.

It is best to rename your UserForm e.g. to ProgressBar. Next add 3 VBA Labels to the User Form and change some selected properties per the image below:
VBA Progress Bar User Form

Copy the VBA Progress Bar Source Code

Now we need the logic that will allow you to configure and run the Progress Bar. Right click on your new User Form and click View Code. Next copy past the code below:

Configure and Run

Now a quick explanation of the VBA Functions defined above:

  • Initialize (title As String, Optional max As Long = 100)– needs to be run first. Initializes the variables needed to run the Progress Bar and allows you to set the title of the User Form and the max i.e. maximum % progress. By default max is set to 100 but you can change this e.g. you want to process 200 files then set it to 200
  • AddProgress (Optional inc As Long = 1) – add inc incrementally to the amount of total progress. Assuming if you want to process 200 files and set the max value in Initialize and you processed 3 files then run AddProgress with inc equal to 3. When you process another 4 files then run AddProgress with inc equal to 4.

Now that we know the functions needed to run the Progress Bar let us configure and run your Animated Progress Bar in Excel. I added step by step comments to the example code snippet above.

I hope this was helpful. Managing the expectations of you users is key to creating effective automations in Excel VBA. Be sure to also check out ways to enhance the performance of your VBA Macros.

Download VBA Progress Bar

The VBA Progress Bar is part of the VBA Time Saver Kit. Feel free to download the full kit using the link below: