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.

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 the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

Using a Frame Control to Group Option Buttons

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()
             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 option button.



12. Close the UserForm.

Using the GroupName Property to Create Option Button Groups

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.



4. Repeat Step 3 and use mygroup2 for the GroupName.
   
   NOTE: The macro provided below does not address this second group. It is added to the UserForm to illustrate how to programmatically identify option buttons in specific groups (in this case, in mygroup1).



5. Draw a CommandButton control on the UserForm.



6. 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()
   
             Dim x As Control
   
             '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 




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 selected option button from "mygroup1".



10. Close the UserForm.

Setting a Global Variable to the Name of the Selected Option

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




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

    
           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 option button.



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

Additional query words: XL97

Keywords : kbprg kbdta kbdtacode xlvbainfo KbVBA
Version : WINDOWS:
Platform : WINDOWS
Issue type :