Re: Old question new twist?

Oh, I don't mean that I actually do the update. I mean I am maintaining the
app that does the update. That update is quite involved and I have inherited
a VBA app that handles that. The workbooks are then used by other people, not
by me.
Now I had to add 2 more sheets to the workbook and when I added the code for
that, I noticed that my new sheets show 0's and the other sheets don't. I
have tried number formats and setting the DisplayZeros option in code and
either the zeros still show, or I get a Macro warning when I open one of the
files. The details about what I tried and what does not work etc. are in my
original post.

Now, if by "CHange the display Zeros settings" you mean set
Application.ActiveWindow.DisplayZeros = false
then I have to refer you to my original post. That resulted in a Macro
warning and I can not have that come up, nor can I set everybodies security
setting lower to prevent the warning. I guess that means the code to do this
can not be a macro, or be considered a Macro by Excel.

Was that the part that confused you? Did I clear things up for you? I hope
so, if not, please ask again.

Thanks


"Peter T" wrote:

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






.