Microsoft KB Archive/45844

From BetaArchive Wiki

Excel: Custom Number Formats in Custom Dialog Boxes

Last reviewed: November 2, 1994
Article ID: Q45844


Microsoft Excel version 2.20 applies number formats to values in user-defined dialog boxes for only the following items:

   Item               Item Number
   ----               -----------

   Static Text        (Item #5)
   Integer Edit Box   (Item #7)
   Number Edit Box    (Item #8)

Number formats (for example, times, dates, dollar figures, or percentages) applied to any of the following will be displayed as General:

   Item               Item Number
   ----               -----------

   OK Button          (Items #1 or #3)
   Cancel Button      (Items #2 or #4)
   Text Edit Box      (Item #6)
   Group Box Title    (Items #11 or #14)
   Option Button      (Item #12)
   Check Box          (Item #13)
   List Box Title     (Items #15 or #16)

For each of the above, times and dates display as serial numbers, and dollar figures and percentages display as decimal equivalents. Note that this is different from Excel version 1.50, which displayed formatted values for all dialog box items.


To display formatted values in any other form than Static Text or an Integer or Number Edit box, use the SET.VALUE() and TEXT() functions immediately before calling the dialog box to enter the formatted value as text in the proper column of the dialog box item.

For example, the following macro attaches the current time to the check box of a user-defined dialog box:

                          A                 B  C  D  E  F  G  H
   1  =SET.VALUE(G2,TEXT(NOW(),"hh:mm"))
   2  =DIALOG.BOX(B1:H3)                   13
   3  =RETURN()                             3             OK

In the above macro, the range B1:H3 defines a dialog box that contains two items: a check box and an OK button. Cell A1 of the macro places the current time, in the form of text, in cell G3 (the text column for the check box), and A2 calls the dialog box.

KBCategory: kbother


Additional reference words: 2.20 3.00

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