XL2000: Macro Examples Using Option Button Controls on a UserForm

This article was previously published under Q213549
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
There are two ways to group option buttons on a UserForm. To group thecontrols, do either of the following:
  • 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 otheroption buttons in the group are set to False.

Neither method is better than the other one. The method you choose 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 articlealso contains an example that illustrates a third method that does notrequire grouping of option buttons.
MORE INFORMATION
Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Using a Frame Control to Group Option Buttons

In this example, you create a UserForm that contains a command button, andthree option buttons within a Frame control. The advantage of having theoption buttons within the frame is that the Frame control creates acollection of controls for the frame. This collection of option buttons inthe 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 Option Button controls on the Frame control.
  5. Draw a Command Button control on the UserForm (outside the Framecontrol).
  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 command button 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()          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					
  9. Run the Frame_Options macro on the general module.The UserForm is displayed with none of the option buttons selected.

  10. Click any one of the option buttons.
  11. Click the command button.A message box appears with the caption of the currently selected optionbutton.

  12. 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 isharder to programmatically determine which option button is the selectedoption. 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 option button controls on the UserForm and set the GroupName property for each one to mygroup1.
  4. Repeat Step 3 and use mygroup2 for the GroupName.NOTE: The macro provided below does not address this second group. Itis added to the UserForm to illustrate how to programmatically identifyoption buttons in specific groups (in this case, in "mygroup1").

  5. Draw a command button control on the UserForm.
  6. Double-click the command button to display the code module associated with the UserForm, 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 userform.          For Each x in Me.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					
  7. 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					
  8. Run the No_Frame_Options macro.The UserForm appears.

  9. Click any of the first three option buttons (that you added in step 3) on the UserForm, and then click the command button.A message box appears and displays the caption of the currently selectedoption button from mygroup1.

  10. 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 requiresneither a Frame control nor a common GroupName. This method uses macrocode, which is assigned to the Click event for each option button youcreate on the UserForm, to set the value of a global variable to the nameof the selected option button. If you create multiple groups of optionbuttons, 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 option button controls on the UserForm.
  4. Draw a command button control on the UserForm.
  5. Double-click the OptionButton1 control.This step displays the code module that is associated with the UserForm.

  6. Type the following macro code on this module sheet:
           Private Sub OptionButton1_Click()           myoption = "option button 1"       End Sub					
  7. Double-click the OptionButton2 control.This step displays the code module associated with the UserForm.

  8. Type the following macro code on this module sheet:
           Private Sub OptionButton2_Click()           myoption = "option button 2"       End Sub					
  9. Double-click the OptionButton3 control.This step displays the code module that is associated with the UserForm.

  10. Type the following macro code on this module sheet:
           Private Sub OptionButton3_Click()           myoption = "option button 3"       End Sub					
  11. Double-click the command button 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					
  12. On the code module that is associated with the UserForm, click theObject 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
  13. In the General module you inserted into the project in Step 2, type the following code:
           Sub Show_UserForm()           UserForm1.Show       End Sub					
  14. Run the Show_UserForm macro.Your UserForm appears.

  15. Click any one of the option buttons, and then click the command button.A message box appears that displays the name of the selected optionbutton.

  16. Click OK in the message box, and then close the UserForm.
REFERENCES
For more information about grouping option buttons, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type groupname property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Then click to select the GroupName Property topic.
XL2000
Properties

Article ID: 213549 - Last Review: 12/05/2015 12:14:03 - Revision: 3.5

Microsoft Excel 2000 Standard Edition

  • kbnosurvey kbarchive kbdtacode kbhowto kbprogramming KB213549
Feedback