Excel: Dialog Box Does Not Reset Option Group

SUMMARY

In Microsoft Excel, to set a dialog box so that no option in an option group is selected when the dialog box is called, set the initial result for the option group to #N/A using SET.VALUE().

This procedure works only if it is the first time the dialog box is called in a macro. This is because Excel stores a resource for that dialog box so that it doesn't have to redraw the dialog. Excel does not see the SET.VALUE(cell,NA()) as a change that requires the redrawing of the dialog box. Therefore, when the dialog box is called, it will have the same option selected from the preceding call of the dialog box, even though the initial result value is #N/A.

MORE INFORMATION

To work around this behavior, use FORMULA("=NA()"). For example, the following macro will continue bringing up a dialog box with an OK button, a Cancel button, and three option buttons (with no buttons selected) until the Cancel button is clicked. The macro is assumed to be located on a macro sheet named "Macro1".

+---+-----------------------------+---+---+---+---+---+-------+-----+

|   |              A              | B | C | D | E | F |   G   |  H  |

| 1 | =FORMULA("=NA()",Macro1!H4) |   |   |   |   |   |Options|     |

| 2 | =DIALOG.BOX(B1:H6)          | 1 |   |   |   |   |OK     |     |

| 3 | =IF(A2,GOTO(A1))            | 2 |   |   |   |   |Cancel |     |

| 4 | =RETURN()                   | 11|   |   |   |   |       |     |

| 5 |                             | 12|   |   |   |   |Yes    |     |

| 6 |                             | 12|   |   |   |   |No     |     |

This feature is under review and will be considered for inclusion in a future release.