XL97: Problems Using TripleState Property for OptionButton

If you view the properties list for an ActiveX OptionButton control, you will be able to set the TripleState property to either True or False. But, setting it to either True or False has no effect on the control's behavior.

The TripleState property is available for the OptionButton control, but it is essentially disabled for the control. Regardless of the setting of the TripleState property, through the user interface, you cannot set the OptionButton to a value other than True (selected) or False (not selected). This behavior is by design of Microsoft Excel.

The TripleState property is designed to work with the ActiveX CheckBox control. If you set the TripleState property of an ActiveX CheckBox control to True, then you can toggle the value of the CheckBox from True (selected), to False (cleared) to Null (dimmed).

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 (https://partner.microsoft.com/global/30000104)

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice (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 (http://support.microsoft.com/default.aspx?scid=fh;en-us;cntactms) If you set the TripleState property of an OptionButton to True, you can programmatically set the value of the OptionButton to Null. This will give you the same effect as setting the CheckBox control to Null (the OptionButton appears dimmed).

The following example macro demonstrates setting an OptionButton to Null.

Setting up the Control

1. Close and save any open workbooks, and then open a new workbook.
2. If the Control Toolbox is not visible, point to Toolbars on the View menu, and then click Control Toolbox.
3. Click the OptionButton control on the Control Toolbox toolbar, and then draw an OptionButton on Sheet1.
4. Click the Exit Design Mode button on the Control Toolbox toolbar.

Sample Macro Code

1. On the Tools menu, point to Macro, and then click Visual Basic Editor.
2. On the Insert menu click Module.
3. Enter the following code in the module.

      Sub Set_Option()
   
      Dim Choice As Integer
      Dim MyPrompt As String
   
      MyPrompt =  "Type 1 for Selected, 2 for Not Selected and 3 for Grayed"
      Choice = InputBox(MyPrompt)
   
      Select Case Choice
   
         Case 1
            Sheet1.OptionButton1.Value = True
   
         Case 2
            Sheet1.OptionButton1.Value = False
   
         Case 3
            Sheet1.OptionButton1.Value = Null
   
         End Select
   
      End Sub
   						

Running the Macro

1. Activate Microsoft Excel (press ALT+F11).
2. Select any cell in Sheet1.
3. On the Tools menu, point to Macro, and then click Macros.
4. Select Set_Option and click Run.
5. Enter either 1, 2, or 3 in the InputBox when prompted, and click OK.

The OptionButton will be set to the state that matches the number you entered in the InputBox (1 = ON, 2 = OFF, 3 = Grayed).

For more information about OptionButton Controls, click the Index tab in Microsoft Excel Help, type the following text and then double-click the selected text to go to the "Add buttons, check boxes, or other controls to a worksheet" topic.