Assigning Custom Number Formats to Styles and Toolbars
The only problem with custom number formats is that they're just not that easy to use. To apply them to a cell selection, you have to open the Number tab of the Format Cells (Ctrl+1) dialog box, select Custom in the Category list box, and then find the darned number format codes in the Type list box containing that long listing of codes.
To make your custom number formats much more accessible, you assign the custom number formats to styles that you can then select from the Style dialog box (or Style dropdown list if you follow my advice and add this little gem to the Formatting toolbar or one of your own design.
To hitch one of your custom number formats to a new style, follow these steps:
- Apply the custom number format you want to assign to a style to a sample entry in your worksheet.
To do this, select the cell, open the Number tab of the Format Cells dialog box (Ctrl+1), select Custom in the Category list box, select the format's codes in the Type list box, and click OK. - Choose Format → Style to open the Style dialog box.
- Enter the new name for your style in the Style Name text box.
When you type the new style name, the Style dialog box displays the number codes for the custom number format in the Style Includes (By Example) area. - Click OK to close the Style dialog box.
Be sure to save your worksheet after assigning your custom number formats to new styles. Keep in mind that styles are saved only as part.
If you start using a custom number format on a frequent basis, you may want to do more than just assign the format to a style that you can apply from the Style dialog box or drop-down list box that you add to a toolbar. You can add the style to a button that you add to the Formatting toolbar or a toolbar of your own design.
To add a style that applies a number format to a button, follow these three steps:
- Assign the custom number format to a custom style (as described in the preceding steps).
- Record a keystroke macro that selects that style.
- Assign the keystroke macro to a custom button on one of your toolbars.
To see how easy this is, follow along with the steps for assigning a custom number format - one that hides all types of entries in a cell - to a new button on my Custom Formats toolbar (a toolbar of my own design):
- Create the custom number format that hides all types of entries. To do so, open the Number tab of the Format Cells dialog box, select Custom in the Category list box, enter ;;; in the Type text box, and click OK.
- Assign the new custom format to a new style by choosing Format → Style, entering All Hidden in the Style Name text box, and clicking OK.
- Begin recording a keystroke macro that selects this new All Hidden style. To do so, choose Tools → Macro → Record New Macro, enter All_hidden in the Macro Name text box, select Personal Macro Workbook in the Store Macro In drop-down list box, and click OK.
- To finish recording the macro, choose Format → Style, select All Hidden in the Style Name drop-down list box, click OK, and then click the Stop button in the Stop Recording toolbar.
- Choose View → Toolbars → Customize to open the Customize dialog box. If the toolbar to which you want to add the custom button isn't already visible, click the Toolbars tab and select this toolbar's check box.
- Click the Commands tab, select the Macros in Categories list box, and then drag the custom button from the Customize dialog box to the desired position on the toolbar.
- Assign the keystroke macro to the custom button by right-clicking on the button and then selecting Assign Macro on its shortcut menu.
- Select the All_hidden macro in the Macro Name list box and click OK.
- (Optional) Change the button image and button name and then click the Close button in the Customize dialog box.
After you close the Customize dialog box, you can test your new button. Select a cell that contains some sort of data entry and then click the All Hidden button on your toolbar. The entry should immediately disappear from the cell (while still being visible on the Formula bar). To redisplay the cell entry, apply another format, such as General, to it.
The All Hidden button will work in any worksheet that you build provided that you merge the All Hidden style into the new workbook before clicking the button. If you don't do the merge, you'll get a Run-time error '450' when you click the button; Excel is caught running a macro that tries to apply a style that doesn't yet exist in the new file!