Create Check Boxes: MS Excel

Dennis Faas's picture

Are you looking for a great way to get a 'Yes' or 'No' decision from an end user using Excel? In that case, try presenting your question in the form of a Check Box.

If the user selects the box, the answer is Yes. You can store the result of the decision as a value in a cell and use that value in your code or calculations.

To see how the check box works:

  1. Open a blank worksheet
  2. Go to View | Toolbars, and choose Forms.
  3. Click the Check Box tool once, and click and drag in the worksheet to draw a check box.
  4. When you release, click the Forms toolbar's Control Properties tool, or right-click the new check box, and choose Format Control.

You'll have plenty of time later to tweak the text associated with the check box. Your first order of business is to tell Excel where to store the check box's "state." On the Control tab, enter A1 in the Cell Link field, and click OK.

When you select the check box, you'll see the value in A1 change to TRUE when checked and to FALSE when unchecked. You can use that value in your formulas. For example, you can display a custom message with a formula such as the following:

=IF(A1,"Checked message","Unchecked message")

By the way, the check box Control Properties also let you set the initial value (state) for the check box. If you choose Mixed, the check box will initially appear grayed out and return a value of #N/A to the linked cell, which tells you that the item is neither checked nor unchecked.

Visit Carol's web site to learn more tips like this one!

| Tags:
Rate this article: 
No votes yet