Knowledge Base

XL98: Macro Examples Using OptionButton Controls on a User Form

Article ID: 190276

Article Last Modified on 6/17/2005

APPLIES TO

Microsoft Excel 98 for Macintosh

This article was previously published under Q190276

SUMMARY

There are two ways to group option buttons on a user form. To group the controls, use one of the following methods:

Place the option buttons inside a Frame control. -or-

Use the GroupName property to create a group.

Both of these methods create a group for the specified option buttons, which makes each of the option buttons (within a group) mutually exclusive. This means that when you click any option button in the group, all other option buttons in the group are set to "False."

The method you use depends on how you want to implement the project and whether you want to use a Frame control for each group on the user form or whether you want to use macro code.

This article contains macro examples that use both methods. This article also contains an example that illustrates a third method that does not require grouping of option buttons.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Using a Frame Control to Group Option Buttons

To use a Frame control to group option buttons, follow these steps:

In this example, you create a user form that contains a command button, and three option buttons within a Frame control. The advantage of having the option buttons within the frame is that the frame control creates a collection of controls for the frame. This collection of option buttons in the Frame control works well with the For Each...Next loop.

Open a new workbook and start the Visual Basic Editor.
Insert a user form and module sheet into the project.
Draw a Frame control on the user form.
Draw three option button controls on the Frame control.
Draw a command button control on the user form (outside the Frame control).

On the module sheet you inserted in step 2, add the following code:

        Sub Frame_Options()
            UserForm1.Show
        End Sub

Double-click the command button on the user form to display the code module that is associated with the user form.

On the code module, type the following code:

       Private Sub CommandButton1_Click()
          Dim x As Control

          For Each x in Frame1.Controls ' Loop through the option buttons
                                        ' within the Frame
              If x.Value = True Then
                  MsgBox x.Caption      ' Display the name of the selected
              End If                    ' option button
          Next
       End Sub

Run the Frame_Options macro on the general module.

The user form is displayed with none of the option buttons selected.
Click any one of the option buttons.
Click the command button.

A message box appears with the caption of the currently selected option button.
Close the user form.

Using the GroupName Property to Create Option Button Groups

To use the GroupName property to create option button groups, follow these steps.

If you do not use a Frame control to group your option buttons, then it is harder to programmatically determine which option button is the selected option. You can set the GroupName property for a set of option buttons to the same value. This ensures that if you click one of the option buttons the other buttons are turned off (all option buttons in a group are mutually exclusive). However, you must determine which one is the selected option button.

Open a new workbook and start the Visual Basic Editor.
Insert a user form and a module sheet into the project.
Draw three option button controls on the user form and set the GroupName property for each one to mygroup1.
Repeat Step 3 and use mygroup2 for the GroupName.

NOTE: The following macro does not address this second group. It is added to the user form to illustrate how to programmatically identify option buttons in specific groups (in this case, in "mygroup1").
Draw a command button control on the user form.

Double-click the command button to display the code module associated with the user form, and type the following code for the command button click event:

        Private Sub CommandButton1_Click()
            Dim x As Control

            ' Loop through all of the controls in the user form.
            For Each x in UserForm1.Controls

                ' Check for the string "Option" within the caption of each
                ' control.
                If InStr(x.Caption, "Option") Then

                    ' Check the group name.
                    If x.GroupName = "mygroup1" Then

                        ' Check the status of the option button.
                        If x.Value = True Then

                            MsgBox x.Caption

                        End If
                    End If
                End If
            Next
        End Sub

On the module sheet you inserted into the project in step 2, type the following code:

        Sub No_Frame_Options()
            UserForm1.Show  'Displays the UserForm
        End Sub

Run the No_Frame_Options macro.

The user form appears.
Click any of the first three option buttons (that you added in step 3) on the user form, and then click the command button.

A message box appears and displays the caption of the currently selected option button from "mygroup1".
Close the user form.

Setting a Global Variable to the Name of the Selected Option

A third method for working with option buttons on a user form requires neither a Frame control nor a common GroupName. This method uses macro code, which is assigned to the Click event for each option button you create on the user form, to set the value of a global variable to the name of the selected option button. If you create multiple groups of option buttons, you can use a different global variable for each group.

To set a global variable to the name of the selected option, follow these steps:

Open a new workbook and start the Visual Basic Editor.
Insert a user form and a module sheet into the project.
Draw three option button controls on the user form.
Draw a command button control on the user form.
Double-click the "OptionButton1" control.

This step displays the code module that is associated with the user form.

Type the following macro code on this module sheet:

        Private Sub OptionButton1_Click()
            myoption = "option button 1"
        End Sub

Double-click the "OptionButton2" control.

This step displays the code module associated with the user form.

Type the following macro code on this module sheet:

        Private Sub OptionButton2_Click()
            myoption = "option button 2"
        End Sub

Double-click the "OptionButton3" control.

This step displays the code module that is associated with the user form.

Type the following macro code on this module sheet:

        Private Sub OptionButton3_Click()
            myoption = "option button 3"
        End Sub

Double-click the command button to display the code module that is associated with the user form and type the following code for the command button click event:

        Private Sub CommandButton1_Click()
            MsgBox myoption   'display the current value of the global
                              'variable
        End Sub

On the code module that is associated with the user form click the Object drop-down, click "(General)," and then click "(Declarations)" in the Procedure list. Type the following code in this section of the code module:
```
        Public myoption As String
```

In the General module you inserted into the project in Step 2, type the following code:

        Sub Show_UserForm()
            UserForm1.Show
        End Sub

Run the Show_UserForm macro.

Your user form appears.
Click any one of the option buttons, and then click the command button.

A message box appears that displays the name of the selected option button.
Click OK in the message box, and then close the user form.

REFERENCES

For more information about grouping option buttons, from the Visual Basic Editor, click the Office Assistant, type groupname, click Search, and then click to view "Ways to create an option group."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, please see the following article in the Microsoft Knowledge Base:

176476 OFF: Office Assistant Not Answering Visual Basic Questions

Additional query words: XL98

Keywords: kbprogramming KB190276

Microsoft KB Archive/190276

Contents

XL98: Macro Examples Using OptionButton Controls on a User Form

SUMMARY

MORE INFORMATION

Using a Frame Control to Group Option Buttons

Using the GroupName Property to Create Option Button Groups

Setting a Global Variable to the Name of the Selected Option

REFERENCES