Additional Information:
This video shows how a user form can be used to add data to a spreadsheet. It builds on knowledge gained in the previous module and expects a basic understanding of field properties e.g. we give the form a caption using the Caption property.
Generally forms are used as labor saving devices, so it is useful if users can select data from dropdown lists. As with data validation, you can specify a list using a range name. This should be entered in the RowSource box. We can force users to select from the specified list by setting the MatchRequired property to True. Note that if you are using MatchRequired, it is often worth including a blank entry in your list. Visual Basic has a habit of interpreting blank fields as data once a user has clicked in the field. This can confuse it and result in errors.
You will notice we create a number of sub routines within the frmFlights code module. Some of these routines rely on user actions, such as clicking on a button, some of them don't. This is just like the way we trigger some macros using buttons and others, such as the Worksheet Change event, would run when a user takes a certain action.
When writing standard Excel sheet modules, we could deactivate a Worksheet Change macro using Application.EnableEvents = False. However, we cannot disable macros within forms. So when we write a Field Change macro, the code should not make any further changes to that field. Otherwise the macro may run again and again and again. There are ways around this problem, but it is certainly worth being aware of.
This video makes use of comboboxes, textboxes, labels, checkboxes and command buttons. It does not use option buttons. An option button is similar to a checkbox in that it has a label e.g. Yes or No, and can be selected. When it is selected, its value is True, when it is deselected, its value is False. The difference between a checkbox and an option button is that checkboxes are independent, whereas option buttons can be linked. If you have a simple Yes/No question then option buttons can force a user to choose between the two, whereas checkboxes would let a user select both.
As option buttons are not independent, they have to be grouped. If you assign two option buttons the same GroupName (in the Properties Window), Excel will only allow the user to select one or the other.
These principles may already be familiar because form controls behave exactly the same as ActiveX controls within Excel itself. When we add ActiveX command buttons to Excel sheets, they are actually Visual Basic controls. You can view their properties by right clicking and selecting Properties. Therefore, if you have used option buttons to create spreadsheet questionnaires, you may already be familiar with the concept of grouping.
