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

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.

Number formatting works with formulas as well as numbers. Just as long as
the result is a value.

This number format suppresses the display of 0 (note the final semicolon):

#,##0.00_);(#,##0.00);

But the cell has to equal _exactly_ zero, not e.g., .0000001. To insure a
near-zero is zero use ROUND:

=ROUND(SUM(A1:A10),0)

--
Jim
"URW" wrote in message
...
| 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

Thanks for the reply Jim, but it does not work. I modified the formula in one
of the cells as you suggested and the zero still shows.

BTW, the summation value was exactly zero anyway because I am summing only 1
column which is 0 in this particular instance. Even if the column has no
values in any of its cells, the sum at the bottom still shows a 0, even if I
round as you suggest.

Any other ideas?


"Jim Rech" wrote:

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.

Number formatting works with formulas as well as numbers. Just as long as
the result is a value.

This number format suppresses the display of 0 (note the final semicolon):

#,##0.00_);(#,##0.00);

But the cell has to equal _exactly_ zero, not e.g., .0000001. To insure a
near-zero is zero use ROUND:

=ROUND(SUM(A1:A10),0)

--
Jim
"URW" wrote in message
...
| 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

I modified the formula

My suggestion was to modify the format, not the formula. Send me a workbook
that demonstrates this phenomenon and I'll try to fix it. I don't need the
entire workbook, just the few cells that recreate the problem.

--
Jim
"URW" wrote in message
...
| Thanks for the reply Jim, but it does not work. I modified the formula in
one
| of the cells as you suggested and the zero still shows.
|
| BTW, the summation value was exactly zero anyway because I am summing only
1
| column which is 0 in this particular instance. Even if the column has no
| values in any of its cells, the sum at the bottom still shows a 0, even if
I
| round as you suggest.
|
| Any other ideas?
|
|
| "Jim Rech" wrote:
|
| 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.
|
| Number formatting works with formulas as well as numbers. Just as long
as
| the result is a value.
|
| This number format suppresses the display of 0 (note the final
semicolon):
|
| #,##0.00_);(#,##0.00);
|
| But the cell has to equal _exactly_ zero, not e.g., .0000001. To insure
a
| near-zero is zero use ROUND:
|
| =ROUND(SUM(A1:A10),0)
|
| --
| Jim
| "URW" wrote in message
| ...
| | 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
|
|
|

JIm,

I will try to get you the minimum code and workbook but I don't think I can
get that to you today. I will be in training most of the day.

I also have to ask how I can get the workbook to you. I don't see anyway to
attach something to my post. Sorry, but I am fairly new to newgroups as well.

Thanks


"Jim Rech" wrote:

I modified the formula

My suggestion was to modify the format, not the formula. Send me a workbook
that demonstrates this phenomenon and I'll try to fix it. I don't need the
entire workbook, just the few cells that recreate the problem.

--
Jim
"URW" wrote in message
...
| Thanks for the reply Jim, but it does not work. I modified the formula in
one
| of the cells as you suggested and the zero still shows.
|
| BTW, the summation value was exactly zero anyway because I am summing only
1
| column which is 0 in this particular instance. Even if the column has no
| values in any of its cells, the sum at the bottom still shows a 0, even if
I
| round as you suggest.
|
| Any other ideas?
|
|
| "Jim Rech" wrote:
|
| 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.
|
| Number formatting works with formulas as well as numbers. Just as long
as
| the result is a value.
|
| This number format suppresses the display of 0 (note the final
semicolon):
|
| #,##0.00_);(#,##0.00);
|
| But the cell has to equal _exactly_ zero, not e.g., .0000001. To insure
a
| near-zero is zero use ROUND:
|
| =ROUND(SUM(A1:A10),0)
|
| --
| Jim
| "URW" wrote in message
| ...
| | 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
|
|
|

Jim,

I have the workbook ready for you. Where to you want me to send it?

"Jim Rech" wrote:

I modified the formula

My suggestion was to modify the format, not the formula. Send me a workbook
that demonstrates this phenomenon and I'll try to fix it. I don't need the
entire workbook, just the few cells that recreate the problem.

--
Jim
"URW" wrote in message
...
| Thanks for the reply Jim, but it does not work. I modified the formula in
one
| of the cells as you suggested and the zero still shows.
|
| BTW, the summation value was exactly zero anyway because I am summing only
1
| column which is 0 in this particular instance. Even if the column has no
| values in any of its cells, the sum at the bottom still shows a 0, even if
I
| round as you suggest.
|
| Any other ideas?
|
|
| "Jim Rech" wrote:
|
| 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.
|
| Number formatting works with formulas as well as numbers. Just as long
as
| the result is a value.
|
| This number format suppresses the display of 0 (note the final
semicolon):
|
| #,##0.00_);(#,##0.00);
|
| But the cell has to equal _exactly_ zero, not e.g., .0000001. To insure
a
| near-zero is zero use ROUND:
|
| =ROUND(SUM(A1:A10),0)
|
| --
| Jim
| "URW" wrote in message
| ...
| | 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
|
|
|

This reply is actually more for others that read this. Jim solved the problem
for me after I emailed him a scaled down version of the code. For etails,
please look at my reply to Peter's last post. But let me take this
oppertunity to thank Jim for his help. Jim, you are awesome! I really
appreciate your willingness to help, going beyond what I would have expected
from a newgroup.

Thanks a million

"Jim Rech" wrote:

I modified the formula

My suggestion was to modify the format, not the formula. Send me a workbook
that demonstrates this phenomenon and I'll try to fix it. I don't need the
entire workbook, just the few cells that recreate the problem.

--
Jim
"URW" wrote in message
...
| Thanks for the reply Jim, but it does not work. I modified the formula in
one
| of the cells as you suggested and the zero still shows.
|
| BTW, the summation value was exactly zero anyway because I am summing only
1
| column which is 0 in this particular instance. Even if the column has no
| values in any of its cells, the sum at the bottom still shows a 0, even if
I
| round as you suggest.
|
| Any other ideas?
|
|
| "Jim Rech" wrote:
|
| 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.
|
| Number formatting works with formulas as well as numbers. Just as long
as
| the result is a value.
|
| This number format suppresses the display of 0 (note the final
semicolon):
|
| #,##0.00_);(#,##0.00);
|
| But the cell has to equal _exactly_ zero, not e.g., .0000001. To insure
a
| near-zero is zero use ROUND:
|
| =ROUND(SUM(A1:A10),0)
|
| --
| Jim
| "URW" wrote in message
| ...
| | 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
|
|
|

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" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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 sheet 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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