global zero suppress Excel 2007
 

I know I can go to the Office Button, then to Advanced, then to "Display
Options for this Workbook" and remove the check from the box for "Show a zero
in cells that have zero value", but I'm curious if there is a way to make
that the default for all worksheets by default so I can avoid manually
setting it each time??????
--
''''Bye from Big Al

global zero suppress Excel 2007
 

You could make the changes to your basic Excel new workbook template, then
new workbooks would start out with all sheets set up that way and any sheets
you add to the workbook via "move or copy" would also be set up that way.

Similarly, you could start a new workbook, set the sheets up to suppress the
display of zero values and save it as a regular workbook. Then use File --
New -- From Existing Workbook and it will then work like a template file.
Or you could actually save this file as a template file and use File -- New
-- From Templates on my computer to start new files.

I believe that new worksheets added by clicking the add sheet tab would
still have to be set via the Office button/Excel Options method for all of
these, but as long as you keep one sheet 'empty' and use 'move or copy' to
add sheets to the workbook, the setting would carry over to the added sheets.

Finally, you could put this macro in your Personal workbook, or add it to
any workbook, to set all sheets in it to hide zero values:

Sub HideZeroValues()
'
Dim anySheet As Worksheet
Dim startSheet As String
startSheet = ActiveSheet.Name
Application.ScreenUpdating = False
For Each anySheet In ActiveWorkbook.Worksheets
anySheet.Activate
ActiveWindow.DisplayZeros = False
Next
ActiveWorkbook.Worksheets(startSheet).Activate
End Sub


"Big Al" wrote:

I know I can go to the Office Button, then to Advanced, then to "Display
Options for this Workbook" and remove the check from the box for "Show a zero
in cells that have zero value", but I'm curious if there is a way to make
that the default for all worksheets by default so I can avoid manually
setting it each time??????
--
''''Bye from Big Al

global zero suppress Excel 2007
 

Open a new workbook. Customize as you wish.........group the sheets before
setup so's all sheets get same settings.

FileSave As Type: scroll down to Excel Template(*.XLT) and select. Name
your workbook "BOOK"(no quotes). Excel will add the .XLT to save as
BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for FileNew or the Toolbar button
FileNew or CTRL + n

WARNING................Do not use FileNew...Blank Workbook or you will get
the Excel default workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default InsertSheet.

More can be found on this in Help under "templates"(no quotes).

Instructions are for Excel 2003 and earlier.

2007 is similar except you would save as BOOK.xltx

New File Icon or CTRL + n in 2007.


Gord Dibben MS Excel MVP

On Wed, 21 Oct 2009 18:39:01 -0700, Big Al
wrote:

I know I can go to the Office Button, then to Advanced, then to "Display
Options for this Workbook" and remove the check from the box for "Show a zero
in cells that have zero value", but I'm curious if there is a way to make
that the default for all worksheets by default so I can avoid manually
setting it each time??????