Ozgrid, Experts in Microsoft Excel Spreadsheets

 Excel Training VBA 2 Lesson 2


When to use Userform & What To Use Them For


Workbook Download
    This is a zipped Excel Workbook to go with this lesson.

When To Use a UserForm?

There is not really a definitive answer to this question as the use of the UserForm allows such flexibility that they can be used is a wide range of situations. They can be for simply creating you own message box through to replacing the entire interface of Excel. You will no doubt find that you will use a UserForm in Excel more and more as you become more comfortable with them. The only restriction will probably be your own imagination and knowledge of the Controls that can be used on them.

If you want to give your projects a professional and consistent look and feel then UserForms will certainly do this. However, having said this, be careful you don't use a UserForm for this reason only! While a professional look and feel is great, the professionalism soon dissipates if the code is not there to support it. So my advice is, if you feel confident enough to use a UserForm then there is probably no reason why you shouldn't.

Having said the above though you should be aware that by using UserForms in a project often means a lot more coding than would normally be needed. This is because we must ensure that they enter valid data and also that we place that valid data in a collection area (data base) for them. While this probably sounds a relatively simple task there are a lot of angles that we need to cover. At the end of the day we have to assume the user will try entering invalid data and this unfortunately happens!

 What to Use a UserForm For?


Again there is not really a definitive answer to this question. Basically they are used for collecting data from a user. They are possibly best suited for controlling the data that we want to collect from the user. We can do this by inserting code into the Private Module of the UserForm that validates what the user can enter. Not only do they allow us to control what the user can enter but we can then transfer this information onto a spreadsheet in a manner that ensures that data is entered in the correct place, format and manner. When the UserForm is used correctly we can make it virtually impossible for erroneous data to be entered, while at the same time making the inputting for the user very easy indeed.

UserForms and their associated Controls can be seen extensively throughout Excel. Activate any menu item that displays a Dialog box and we are looking at one. This can be a useful way for us to try and decide if a UserForm is suited to the task at hand. It can also be used to guide us in which Control to use in a particular situation.

As I have mentioned above, as you become more comfortable with UserForms and their Controls, you will start to use them for a variety of cases for tasks as simple as instructing a User through to creating your own Wizards that will guide the user through a series of steps.

Information Helpful? Why Not Donate | Free Excel Help