XL97: Macro Examples Using OptionButton Controls on a UserForm

• Microsoft Excel 97 for Windows

SUMMARY

There are two ways to group option buttons on a UserForm. To group the controls, do either of the following:

• Place the option buttons inside a Frame control.

  -or-

• Use the GroupName property to create a group.

Neither method is better than the other one. 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 UserForm or 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 examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Using a Frame Control to Group Option Buttons

In this example, you create a UserForm that contains a CommandButton, and three OptionButtons 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.

1. Open a new workbook and start the Visual Basic Editor.

2. Insert a UserForm and module sheet into the project.

3. Draw a Frame control on the UserForm.

4. Draw three OptionButton controls on the Frame control.

5. Draw a CommandButton control on the UserForm (outside the Frame control).

6. On the module sheet you inserted in Step 2, add the following code:

          Sub Frame_Options()
              UserForm1.Show
          End Sub
   
   

7. Double-click the CommandButton on the UserForm to display the code module that is associated with the UserForm.

8. On the code module, type the following code:

   Private Sub CommandButton1_Click()

             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
   
   

9. Run the Frame_Options macro on the general module.

1. Click any one of the option buttons.

2. Click the command button.

1. Close the UserForm.

Using the GroupName Property to Create Option Button Groups

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 option button the other buttons are turned off (all option buttons in a group are mutually exclusive). However, you must determine which option button is the one that is turned on.

1. Open a new workbook and start the Visual Basic Editor.

2. Insert a UserForm and a module sheet into the project.

3. Draw three OptionButton controls on the UserForm and set the GroupName property for each one to "mygroup1" (without the quotation marks).

4. Repeat Step 3 and use "mygroup2" (without the quotation marks) for the GroupName.

1. Draw a CommandButton control on the UserForm.

2. Double-click the CommandButton to display the code module associated with the UserForm, and type the following code for the command button click event:

         Private Sub CommandButton1_Click()
   

             'Loop through all of the controls in the userform.
             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
   
   

3. 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
   
   

4. Run the No_Frame_Options macro.

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

1. Close the UserForm.

Setting a Global Variable to the Name of the Selected Option

A third method for working with option buttons on a UserForm 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 UserForm, 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.

1. Open a new workbook and start the Visual Basic Editor.

2. Insert a UserForm and a module sheet into the project.

3. Draw three OptionButton controls on the UserForm.

4. Draw a CommandButton control on the UserForm.

5. Double-click the "OptionButton1" control.

1. Type the following macro code on this module sheet:

   Private Sub OptionButton1_Click()

              myoption = "option button 1"
          End Sub
   
   

2. Double-click the "OptionButton2" control.

1. Type the following macro code on this module sheet:

   Private Sub OptionButton2_Click()

              myoption = "option button 2"
          End Sub
   
   

2. Double-click the "OptionButton3" control.

1. Type the following macro code on this module sheet:

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

2. Double-click the CommandButton to display the code module that is

       associated with the UserForm 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
   
   

3. On the code module that is associated with the UserForm 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:
   

          Dim myoption As String
   

4. In the General module you inserted into the project in Step 2, type

       the following code:
   

          Sub Show_UserForm()
              UserForm1.Show
          End Sub
   
   

5. Run the Show_UserForm macro.

1. Click any one of the option buttons, and then click the command button.

1. Click OK in the message box, and then close the UserForm.

REFERENCES

For more information about grouping option buttons, click the Index tab in Microsoft Excel Visual Basic Help, type the following text

   groupname property