Re: Old question new twist?

Like I said before Peter, I tried that but it resulted in a macro warning
when the file was opened next time. I used

Application.ActiveWindow.DisplayZeroes = False

to set the display setting for the new sheets when I add them in the code.
But I got a macro security warning later, which I can not have and I can not
lower security setting either to get around the warning.

Is that not what you are suggesting? Or do you have another way to set the
display for zeros in code?



"Peter T" wrote:

Afraid I still don't see why you can't do something along the lines I have
been trying to suggest. Nothing in your subsequent posts has explained why
not, if anything they have served to reinforce the idea.

You say you have inherited a VBA app. Ideally add a new routine in that app
to change the display zeros setting at same time a *** is added or data is
updated (could even loop existing sheets to double check they are still OK).
Alternatively make a small app of your own.

Regards,
Peter T

"URW" <URW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2A4B7D29-EDCA-46CE-9E3F-F97F97830659@xxxxxxxxxxxxxxxx
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









.