Microsoft KB Archive/51547

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 16:57, 18 July 2020 by 3155ffGd (talk | contribs) (importing KB archive)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)


Excel: Dialog Box Does Not Reset Option Group

Last reviewed: November 2, 1994
Article ID: Q51547

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.


KBCategory: kbother

KBSubcategory:

Additional reference words: 2.20 3.00


Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.