1.8 Resize
Home and Learn - Free Excel VBA Course
10.7 Coding For Option Buttons in VBA
Ongoing tutorial - First part is here: Part One
Option buttons tend to work as a group. When one button is selected the other (or others) become deselected.
So that your buttons are treated as a group, return to your form in the VBA Editor and click on the YES option button. Now have a look at the properties for the button. Locate the GroupName property and type OB1. Press the enter key on your keyboard and do the same for the NO option button. Both buttons will then have a value of OB1 for their GroupName property (OB1 is just something we came up with - you can call your group of option buttons anything you like.):
Go back to your coding window and create a new Private Sub. Call it GetOptionButtonValue. What we need to do, here, is to get the value from Column 7 on our spreadsheet (the G Column). We'll then use an If Statement to test this value. Here's the full code for this new Sub:
Private Sub GetOptionButtonValue( )
Dim OB As Variant
OB = ActiveCell.Offset(, 6).Value
If OB = "Yes" Then
OptionButton1.Value = True
Else
OptionButton2.Value = True
End If
End Sub
We've set up a Variant variable and called it OB. The second line gets the value from the spreadsheet:
OB = ActiveCell.Offset(, 6).Value
The active cell is in column 1, remember, so we use Offset to move 6 columns over into the G Column. We then get the Value of this cell.
The If Statement checks what is inside of the OB variable:
If OB = "Yes" Then
OptionButton1.Value = True
Else
OptionButton2.Value = True
End If
If the value is "Yes" then we set the Value property of OptionButton1 to True. If it's not "Yes" then we set the value of OptionButton2 to True. This is enough to either switch on an Option Button or switch it off.
We now need to call this new Sub into action. Click inside of your cmdLoad code and add the following line just before End Sub:
Call GetOptionButtonValue
Your code will then look like this:
You can test it out again. Run your form and click the Load Image Information button. You should find that the correct option button is selected for the Flash item.
We can now load an image into the image box. We'll do that in the next lesson below.
Next Lesson: 10.8 Load Image >