This is a zipped Excel Workbook to go with this lesson.
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!
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.
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
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.