Old question new twist?

[URW]

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 Rech]

I tried using a format string, setting the numberFormat propery for the
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
| 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

 

[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?

 

[Peter T]

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]

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]

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]

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]

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

 

[Jim Rech]

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
| 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
| > | > | 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
| >
| >
| >

 

[URW]

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?

 

[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

 

[Peter T]

Oh dear - the whole point of what I have been trying to suggest is DO NOT
put any code in your 50 workbooks, ie the ones you don't want your users to
be concerned with seeing macro warnings. Put code to hide zeros in a single
workbook that only you have. Obviously this wb may trigger a warning when
you open it.

But you say you already have a large amount of VBA that does the update so
presumably you already get a warning about that project. Also, as I
mentioned previously, you could put the additional don't show zeros code in
the big update project.

As an alternative and expanding on Jim's idea to use numberformat to hide
the zero, try this

Format > Styles..
Stylename: Normal
Modify
Numberformat: General;General;

Change the +ve & -ve general if/as required, perhaps per Jim's example but
ensure you have the at least two semicolons in the string with nothing after
the 2nd semicolon (btw did you do it like that with Jim's)

From now on any cells that don't have a number format will not display a
number that's exactly zero, even in newly added sheets.

If that works for you could process all existing files and save a template
for future use.

Did you say you are updating with data from Access - are your zeros
definitely arriving into cells as numbers an not as strings like '0. No way
to hide those until converting them to numbers other than masking with white
font.

Regards,
Peter T

 

[URW]

See below please

Oh dear - the whole point of what I have been trying to suggest is DO NOT
put any code in your 50 workbooks, ie the ones you don't want your users to
be concerned with seeing macro warnings. Put code to hide zeros in a single
workbook that only you have. Obviously this wb may trigger a warning when
you open it.

Now I don't follow you Peter. The Zero display setting is sheet specific as
far as I can determine so how will setting the option in a new workbook
affect the 50 user workbooks at a sheet level? I also don't understand how
settings in a workbook I have will affect the workbooks the other users have.

But you say you already have a large amount of VBA that does the update so
presumably you already get a warning about that project. Also, as I
mentioned previously, you could put the additional don't show zeros code in
the big update project.

I had no warning until I added that one line of code and I have removed that
code and gotten rid of the warning, though that took a lot more effort than I
expected. Also there is only one application/project. I added code to the
existing application to generate the new sheets and that application now adds
the sheets if needed and then updates all the sheets with data from the DB.
It cycles thru all the sheets in a workbook and thru all the workbooks that
need to be updated. So, there is only one application and when I put the
don't show zeros code into the add sheet code I added it to the update
project as well, since they are one and the same.

As an alternative and expanding on Jim's idea to use numberformat to hide
the zero, try this

Format > Styles..
Stylename: Normal
Modify
Numberformat: General;General;

Change the +ve & -ve general if/as required, perhaps per Jim's example but
ensure you have the at least two semicolons in the string with nothing after
the 2nd semicolon (btw did you do it like that with Jim's)

Ok, I will try that. And when I tried Jim's format string, I used it exactly
as he showed it.

From now on any cells that don't have a number format will not display a
number that's exactly zero, even in newly added sheets.

If that works for you could process all existing files and save a template
for future use.

I am still confused how setting options and formats in my local Excel
application or my local workbooks will affect workbooks that other people are
using with their own copy of Excel, but I am willing to try almost anything.

Have I mentioned that my knowledge of Excel is limited? I really have not
done much with it and never messed with options or format strings before, at
least not beyond the most basic stuff. That is perhaps why I am not making
sense to you and I don't get the idea of using a local workbook to change
things in the workbooks I am updating.

One more thing: When you say: Process all existing files... what exactly do
you mean? Are you suggesting I just run my usual update, or are you
suggesting I modify the Format Style for those workbooks as well? I think you
are suggesting the former, not the later but I am not entirely sure.

Did you say you are updating with data from Access - are your zeros
definitely arriving into cells as numbers an not as strings like '0. No way
to hide those until converting them to numbers other than masking with white
font.

The data comes as numbers from an SQL Server db and is stored in the Access
DB as numbers. The update gets the data as numbers from the Access DB and
updates the sheets with that data. That much I am sure off. I know SQL alot
better than I know Excel.

 

[URW]

Jim,

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

 

[Peter T]

In line -

"URW" wrote in message

See below please


Now I don't follow you Peter. The Zero display setting is sheet specific as
far as I can determine so how will setting the option in a new workbook
affect the 50 user workbooks at a sheet level? I also don't understand how
settings in a workbook I have will affect the workbooks the other users

have.

Indeed Zero display setting is sheet specific, or rather specific the
window(s) of a given sheet. However the code to change the setting does not
need to be in the same workbook. Try this -

Put this code a workbook that only you will use, not one of the 50 data wb's

Change "myData01.xls" to the name of an open data wb or some other wb for
testing

Sub test()
Dim wb As Workbook

'' if you know the name use -
Set wb = Workbooks("myData01.xls")

'' if it is already known for certain that the wb to process
'' is active, OK to use simply -
' Set wb = ActiveWorkbook

ShowZeros wb, False ' True to show zeros

End Sub


Sub ShowZeros(wb As Workbook, bDisplay As Boolean)
Dim wn As Window
Dim ws As Worksheet
Dim shtOrig As Object

'' comment screenupdating = False while testing
'' not necessary but reduces flicker
Application.ScreenUpdating = False

wb.Activate
Set shtOrig = wb.ActiveSheet

For Each ws In wb.Worksheets
ws.Activate
For Each wn In wb.Windows
' loop just in case user has opened multiple windows
wn.DisplayZeros = bDisplay
Next
Next

shtOrig.Activate ' reactivate the original activesheet

Application.ScreenUpdating = True
End Sub

Obviously 'you' will get a macro warning when you re-open the book with this
code - but only you.

It's very rarely necessary to activate or select things except when working
with Window properties, hence use of Activate in the demo.

I had no warning until I added that one line of code and I have removed that
code and gotten rid of the warning, though that took a lot more effort than I
expected.

I suspect the project had a certificate and you macro security level is such
as to not show a warning with certified projects. Amending any code will
break the certificate. But if it's only you that opens and runs the code
just ignore the warning.

Have you looked into adapting Jim's numberformat in the Normal font. Once
done will never need to change anything in the workbook (unless user
changes). New wb's can be started from a similarly adapted template.

Regards,
Peter T

 

[URW]

Peter,

I had emailed Jim a test version of my application and Workbook and he took
a look at it. He suggested setting displayZeros to false, just like you had
and I had tried before. It got me a macro warning though and I commented the
line of code and started to look for a different solution.
Well, I reminded Jim off all that and then decided to try it one more time
just for the heck of it. And it works this time without a Macro warning. I
have no idea why i got the warning last time and don't get it now and Jim
does not see a reason for it either. It is weird and strange and I don;t
understand it, but what the heck. The code works now and until I have
problems again, I will call it fixed and done.

I will try your trick though and store the post for future reference,
because I am sure I will run into Macro warnings again and having a way
around them will be helpful.

I thank you very much for your help. You were great! Your patience,
expertise, help and efforts are very much appreciated.

I am calling this one solved.

 

[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

 

[Peter T]

He suggested setting displayZeros to false, just like you had

and I had tried before. It got me a macro warning though and I commented the
line of code and started to look for a different solution.

If a vbproject includes anything at all, even a commented line or empty
module, the workbook will be flagged to trigger the macro warning (depending
on security level).

I am calling this one solved.

Glad you got it sorted and thanks for the feedback.

Regards,
Peter T