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.):

Option Button Properties

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:

Excel VBA code for an Option Button

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.