Re: Old question new twist?

I really don't follow. You say it is you who updates the workbooks so why
can't you change the display zeros setting at the same time. Would be easy
to loop all sheets to double check the others are still as required.

Regards,
Peter T



"URW" <URW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2E2C11B2-B9D9-40D4-AE2A-8AB702608F1A@xxxxxxxxxxxxxxxx
Peter,
I am not using these workbooks, I just update them. So whatever I have in
my
personal settings does not and can not affect the workbooks I am updating.
The 50 workbooks are used by 50 different people but not by me.

Now having said that, in Office 2003 the DisplayZeroes option is not just
workbook specific it is sheet specific. Out of the 15 worksheets in my
workbook all but the 2 new ones hide 0's, because the old sheets have the
option to hide 0's set. My 2 new sheets do not, because the default is to
show 0's. So even if I could use some global setting to control the 0's it
would not work, because that option is not global to the workbook.

Sorry Peter, but that was not the right answer either. Thanks for trying
though.

Anyone else?


"Peter T" wrote:

You don't need to put the code in each workbook. Eg place in a dedicated
workbook, your personal.xls, some addin, why not in the same project as
you
are running your VBA to do the update stuff.

Regards,
Peter T



"URW" <URW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8BA428E3-73F8-4108-A1EE-7D1A05198313@xxxxxxxxxxxxxxxx
Hi all,

I am new to VBA and Excel programming and have come across the old
problem
of hiding 0's in cells with formulas. I know I can unset the zero
values
option, but I need to do this to 2 sheets in some 50 workbooks, so I
want
to
do this in code. The workbooks are updated every so often using an
Access
database and VBA and the 2 sheets I am dealing with are added to each
during
the update process. I tried using

Application.ActiveWindow.DisplayZeros = False

but that gives me a Macro security warning when the file is opened and
I
don't want that, nor do I want to reduce the security to low to get
around
the warning.

I tried using a format string, setting the numberFormat propery for
the
cells in question to "#,##0_);(#,##0)" but that only works for cells
without
a formula.

Now I am stuck and don't know what else to try. I have found all kinds
of
solutions on the web, but they all involve setting something in Excel
which I
don't want to do, because I would have to do it in 50 or so workbooks.

Does anyone here know how to hide zeros in a cell with a formula in
VBA
code?

The formula is just a summation to total the values in the column, if
that
makes a difference.

If you have some ideas you are willing to share or know for sure this
can't
be done, please reply to my post. I would be very appreciative for the
help.

Thanks

URW





.

Loading