 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
Option Buttons and Checkboxes in Detail
The Difference Between the Two
OptionButtons and CheckBoxes are perhaps one of the most frequently used Controls when designing a UserForm. Unfortunately, many users seem to get the two confused with each other and end up using a CheckBox when it should be an OptionButton and vice verse. The rules of which one to use and when are really quite simple and boils down to basically if the user should be allowed to make one selection only from a choice of many, then it should be OptionButtons. If the user should be allowed to make one or more choices from many, then it should be CheckBoxes. To further this more, an OptionButton cannot be used on its own, it must be part of a group of two or more OptionButtons. A CheckBox on the other hand can and very often is used on its own which enables the user to select or deselect it. If you tried to do this with an OptionButton, the user would only be able to select the OptionButton and without any other OptionButtons forming part of the same group, they would not be able to deselect it.
Let's look now at each of these in detail and also use some examples that we can follow.
OptionButtons
As mentioned above, it is not feasible to use an OptionButton on its own, so when adding one to a UserForm, it means that it will become one of a group of two or more. When an OptionButton is added, one Property which becomes very important is the GroupName of the OptionButton. The correct setting for this would be a one-word string. Each OptionButton on a UserForm which has the same GroupName results in the creation of a group of mutually exclusive OptionButton Controls. The end result of this will be that when one of the group is selected, the remainder of the group will be de-selected. There is no exception to this rule.
Another aspect of this which can often confuse designers is the fact that each OptionButton with the same GroupName is only mutually exclusive to the Container which is housing them. A Container is simply an Object which contains other Objects. The Container is normally a UserForm Control itself. However, it could be a MultiPage Control. For example, we could place a MultiPage Control onto a UserForm containing three separate pages. On each of these pages we could place a group of two or more OptionButtons. We could then give the exact same GroupName to each and every OptionButton. This would then mean that the OptionButtons would not so much be grouped by their GroupName, but rather by the individual pages on the MultiPage housing the OptionButtons. This is because each page of a MultiPage is a Container.
Another Container that can be used to group OptionButtons and often is, is the Frame Control. When a Frame Control is added to a UserForm, we can then place OptionButtons within the Frame Control and then these OptionButtons will automatically become part of the same group. Again, it is important to note that the Frame Control is a Container which can contain other Objects. When using the Frame Control, in conjunction with OptionButtons, it is not necessary to supply a GroupName to the OptionButton, by default the GroupName is always empty text. While this can be useful for a UserForm which may contain several groups of OptionButtons, there are some advantages to using the GroupName Property over the use of a Frame Control.
You reduce the number of Controls on the UserForm and in doing so can enhance performance and reduce the size of the UserForm.
You have far more design flexibility. When you use a Frame Control to create a group, all the OptionButtons must be within the Frame Control's boundary.
You can create buttons with transparent backgrounds which can enhance the visual appearance of your form. It is important to note that the Frame Control is not a transparent Control.
Regardless of which method we choose, an OptionButton simply returns a Boolean value of True or False. The default value for OptionButtons is False. The default Property for an OptionButton is its Value Property.
Lets now use a very simple example to demonstrate this, so that you can see it visually.
Insert a new UserForm into a Workbook and to it add two OptionButtons and ensure their names are OptionButton1 and OptionButton2. Now to the Private Module of the UserForm Object, add the code as shown below.
Private Sub
OptionButton1_Click()
MsgBox OptionButton1
End Sub
You will see that by clicking on the OptionButton you are always returned True in the MessageBox for both buttons. The important thing to note here is that even by clicking either OptionButton when their value is True, the OptionButton remains checked and the Click Event is not fired. What this simply means is that the Click Event will only fire for an OptionButton when it is selected while having a False value. Note also that we have not specified a Property to be returned by the OptionButton but it defaults to the Value Property anyway.
OptionButton with a Control Source
If we use an OptionButton and then also assign a Control Source ie; a cell to the Control, it can behave slightly differently.
For example, using the same two OptionButtons as in the above example, remove the Click Event code for both OptionButtons from the UserForm Private Module. Ensure cells A1 and A2 are empty on the active worksheet and assign OptionButton1's Control Source to A1, and OptionButton2's Control Source to A2. Set the Value Property of OptionButton1 to True. Now run the UserForm and move it out the way so that you can easily see cells A1 and A2 on the active worksheet. Even though we have set the Value Property of OptionButton1 to True, its Control Source will still be empty while the Control Source for OptionButton2 will be reading False. You will notice now that as you click each button, the Value of the button will be reflected in the cell that it is bound to. You will also notice that selecting an OptionButton that currently is False, or unchecked will first deselect the other OptionButton (leaving both OptionButtons unchecked) and it is only when you click the OptionButton for a second time that it returns a Value of True.
CheckBoxes
Lets now look at the CheckBox Control and see some uses for it. One very important thing that we need to understand before we look at the CheckBox Control is that is also has a GroupName Property, however, using the GroupName Property, has no affect whatsoever on the CheckBox controls affecting eachother ie; setting a group of three CheckBoxes to the same GroupName and selecting one would not deselect any others that were currently selected. Basically, the GroupName Property is of no use for the CheckBox. Why it is included, I don't know!!!!
As stated initially, we would use a CheckBox or CheckBoxes when the user is able to select or deselect one or more choices. Again, as with the OptionButton Control, its default Property is the Value Property and its default Event is the Click Event.
Lets now use a very similar example as to the example we used for the OptionButton Controls to show how the CheckBox Control operates.
On a UserForm, place two CheckBoxes and ensure their names are CheckBox1 and CheckBox2 and then in the Private Module of the UserForm Object insert the code as shown below.
Private Sub CheckBox1_Click()
MsgBox CheckBox1
End Sub
Run the UserForm and select/deselect each CheckBox. You will notice that selecting one CheckBox has no effect on the other and we are also able to deselect a CheckBox once it is checked simply by clicking it again.
Although, as with the OptionButton, a CheckBox simply returns True or False, the uses of a CheckBox can vary far and wide. Lets assume that we have a UserForm which, when a button on it is clicked, performs a very lengthy procedure which could sped up greatly by enabling the user to switch calculation to manual by selecting a single CheckBox.