XL: Verifying the Value of a Check Box

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, check boxes can have one of three values: xlOn, xlOff, or xlMixed.

To find the value of a check box, you can use Microsoft Visual Basic for Basic Applications code. (An example of how to do this is shown in the "More Information" section of this article.) Or, if the check box has been linked to a cell, you can use Visual Basic commands to determine the value of that cell.

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 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 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/refguide/default.asp

   Value   Check Box Status
   ------------------------

   TRUE    Selected (On)
   FALSE   Cleared (Off)
   #N/A    Mixed

To link a check box to a cell on a worksheet:

1. In the dialog sheet, select the check box.

2. On the Format menu, click Object or Control.

3. Select the Control tab.

4. In the Cell Link box, enter the appropriate cell reference. For example, "Sheet1!$A$1" (without the quotation marks).

5. Click OK to accept the change.

Sample Visual Basic Procedure

The following example assumes that you have a dialog sheet (Dialog1) that contains one or more check boxes and is located in the same workbook as the Visual Basic module. It checks the value of the check boxes, regardless of whether they have been linked to cells.

To run the example, position the insertion point in the line that reads "Sub ExamineCheckBoxes()" and either press the F5 key or click Start on the Run menu.

   Option Explicit

   Sub ExamineCheckBoxes()

       'Declare the variable used as the For Each loop counter
       Dim Box as CheckBox

       ' Iterate once for each check box in the dialog sheet.
       For Each Box in DialogSheets("Dialog1").CheckBoxes

           ' Get the value of the current check box.
           Select Case Box.Value
               Case xlOn                        ' If the check box is ON
                   MsgBox "Check box is on!"    ' show the "On" message.
               Case xlOff                       ' If the check box is OFF
                   MsgBox "Check box is off!"   ' show the "Off" message.
               Case xlMixed                     ' If the check box is MIXED
                   MsgBox "Check box is mixed!" ' show the "Mixed" message.
           End Select
       ' Repeat the loop until finished.
       Next Box
   End Sub