Excel: Custom Number Formats in Custom Dialog Boxes

Last reviewed: November 2, 1994
Article ID: Q45844

SUMMARY

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.

MORE INFORMATION

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
KBSubcategory:
Additional reference words: 2.20 3.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

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