In my previous post on How to Use Option Buttons, we learnt how to set up an Option Button Group bound to a field in the form's underlying database table. We learnt that an Option Button Group is comprised of two separate types of control - the Option Group control itself, and the individual Option Buttons within it. Once set up, these function together as a single unit.
Today we are going to learn how to set up a similar Option Button Group, but this time, we are going to use VBA to determine which button has been selected, and change the behaviour of the form based upon the selection.
 |
| Screen Colour Option Group |
The process for setting up the form with the Option Group is very similar to that which we went through in my last blog post on How to Use Option Buttons. The main difference is that previously we bound the data selected in the Option Group to a field in the form's underlying database table. This time, however, we are going to leave the Option Group unbound, and reference the data selected in the Option Group with VBA code. To do this we will need to make use of the Option Groups On Click event to trigger the subprocedure containing that code when the user clicks any button in the Option Group.
Setting up the Form and Option Group
Here is the procedure for setting up the Option Group:
1) Create a new form by clicking the FORM DESIGN icon on the CREATE ribbon.
2) Go to the CONTROLS section of the DESIGN ribbon and drag an OPTION GROUP control down onto the design grid.
3) Open the PROPERTY SHEET while the OPTION GROUP CONTROL is highlighted and enter the name "opGrp" in the NAME property (under the OTHER tab)
4) Next go back to the CONTROLS section and drag an OPTION BUTTON control onto the OPTION GROUP which we placed on the grid in the previous step.
5) Enter the name "optRed" in OPTION BUTTON's NAME property whilst the control is highlighted.
6) Repeat steps 4 and 5 twice, creating two more OPTION BUTTONS called "optGreen" and "optBlue" respectively.
 |
| Design View of our Option Group containing the three Option Buttons - optRed, optGreen and optBlue |
The next step in the process is to check the OPTION VALUE property is set up correctly for each of the buttons. To do this you will need to select each Option Button in turn, and go to the property sheet DATA tab. The values are set automatically when you drag the Option Button controls into the Option Group, but just to be sure, check the value for optRed is "1", optGreen is "2" and optBlue is "3".
 |
| The Option Value property for optRed. |
As you may recall from my last blog post, the Option Button selected by the user at runtime determines the Value of the Option Button Group as a whole. As such, if the user selects optGreen, the value of the group is "2" etc. It is this value that our VBA code needs to reference when the subprocedure runs.
Programming the Option Group
Let us now turn our attention to the VBA code which executes when the user clicks one of the Option Buttons at runtime. In order for our code to detect when any button within the Option Button Group is selected, we need to make use of the Option Group's ON CLICK event.
This is done as follows:
1) Select the Option Group Control (which we have called "optGrp") on the design grid so it is highlighted orange.
2) Go to the PROPERTY SHEET and select the EVENTS tab.
3) Click the ellipse button ("...") at the end of the ON CLICK property row to open the VBA editor.
 |
| Selecting the ON CLICK event for the "optGrp" OPTION GROUP. Clicking the ellipse button ("...") opens the VBA editor. |
After clicking the ellipse button on the ON CLICK row of the PROPERTY SHEET, the VBA editor will have opened and automatically created a sub procedure called "optGrp_Click". This is the ON CLICK event-handler. Any code we enter here will be executed when the user clicks any button in the option group at runtime.
Our first programming task is to determine which Option Button the user clicked. This is done by referencing the Option Groups VALUE property as follows:
formname!optiongroupname.Value
In our case the code will be:
Me!opGrp.Value
Referencing the OPTION GROUPS VALUE property in this way gives us the OPTION VALUE of the button selected by the user. As you may recall the OPTION VALUE for the red button was "1", green was "2" and blue was "3". So if the user clicked the green button, the above statement will return "2" when it executes at runtime.
In order to use the OPTION GROUPS VALUE property to change the background colour of the form, we are going to do two things. Firstly we are going to call a separate VBA sub procedure from the OPTION GROUPS ON CLICK event-handler, passing the VALUE property as the parameter; secondly, we are going to create this subprocedure which will process the VALUE property and then change the background colour of the form.
The event-handler code is as follows:
Private Sub opGrp_Click()
Call setBackgroundColour(Me!opGrp.Value)
End Sub
As we learnt above, the first and last line of this event handler is created automatically when we clicked the ellipse button ("...") in the ON CLICK row of the Option Groups property sheet. All we have done is add the middle line to call a sub procedure called setBackgroundColour (yet to be created), and pass the referenced value of the Option Group as the parameter.
Let us take a look at the code for our setBackgroundColour sub procedure:
Private Sub setBackgroundColour(intOpValue)
Dim lngRed As Long, lngGreen As Long, lngBlue As Long
lngRed = RGB(255, 230, 230)
lngGreen = RGB(242, 255, 230)
lngBlue = RGB(230, 240, 255)
Select Case intOpValue
Case 1
Me.Detail.BackColor = lngRed
Case 2
Me.Detail.BackColor = lngGreen
Case 3
Me.Detail.BackColor = lngBlue
End Select
End Sub
Here is a breakdown of how the code works:
1) The first line of the sub procedure receives the option value argument (intOpValue) which was passed to it from the calling statement we wrote in the ON CLICK event handler.
2) The next section sets up three variables of the LONG data type and assigns each one a colour value which is used to set the forms BackColor property to red, green or blue as required.
3) Next we have set up a SELECT CASE statement to determine the value of the intOpValue variable which, as we have learnt above, contains the OPTION VALUE of the OPTION BUTTON clicked by the user at runtime.
4) Then each respective CASE statement tests whether it's value matches intOpValue. When it comes to one that does, the BackColor property of form is set accordingly. So if the value of intOpValue is "2", the second Case statement invokes ... Me.Detail.BackColor = lngGreen ... to set the form's background colour to green.
There is just one more thing we need to do before we can see our form and code in action. This involves calling the setBackgroundColour sub procedure as soon as the form has opened. This is so the form's background colour is set in accordance with the default option button selection in the first instance.
To do this we are going to make use of the forms ON CURRENT event which fires just after the form opens. The procedure is similar to how we set up the ON CLICK event-handler for the Option Group. This time we need to select the form in the design grid by clicking the square at the top right and then open the property sheet for the FORM.
 |
| Selecting the FORM in design view. |
Then we need to open the EVENT tab (of the FORMS property sheet) and click the ellipse ("...") button at the end of the ON CURRENT row. This creates the event-handler in the VBA editor. Once that has been created we just need to enter the same code as previously to call the setBackgroundColour sub procedure as follows:
Private Sub Form_Current()
Call setBackgroundColour(Me!opGrp.Value)
End Sub
That's it! Our form with the programmed option group is now ready to open and use.
No comments:
Post a Comment