• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Never display zeros

K

king.wiemann

New Member
I know how to turn off the display of zeros for individual worksheets. Can I make that the default setting for every worksheet I open - so that I don't have to repeat the process of turning off the display for every worksheet?


King
 
Narayan


That is what I know how to do, but that solution requires I do it on every worksheet that I open (some of which I don't own, so I can't save changes in).


I want an excel default setting (perhaps like a template) that over-rides individual worksheet settings regarding display of zeros.


King
 
Hi, king.wiemann!


As NARAYANK991 correctly posted, I'm afraid that you can't do what you want as the property "Application.DisplayZeros" which accepts true/false values is neither a global property for the application nor for a workbook but for each worksheet.


The best approach would require to intercept the Workbook_SheetActivate event "for each workbook" and place this code there:

-----

[pre]
Code: 
Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveWindow.DisplayZeros = False
End Sub
[/pre]
-----


But it has several cons:

- you'd have to change all your .xlsx to .xlsm

- first time you open a workbook you'd have to copy and paste the code


I think it'll be very complicated to handle at any time, even if later when you've updated all files (many of them I know you can't) it'd be a transparent process.


Other option, which will always be a manual process, maybe inserting that code with another procedure name and not private into your personal macro workbook, PERSONAL.XLSB, assign it an unused Ctrl+<sthng> shortcut and then execute it manually "each time you activate or select a worksheet".


Hope it helps.


Regards!
 
Hi King ,


Have you tried this ?


http://www.mrexcel.com/forum/excel-questions/281118-dont-display-zero-values-set-default.html


The above thread refers to the following link :


http://office.microsoft.com/en-us/excel-help/change-the-default-theme-HA010204550.aspx


Narayan
 
Hi Narayan


I hadn't seen those links. I will give them a try. Thank you!


I think it might be a partial victory - all new workbooks/worksheets will have that default, but the settings of an existing workbook saved by someone else, when I open it, will probably take precedence.


King
 
Hi, king.wiemann!

Both links are interesting and lead you to how to change defaults for workbooks and worksheets as described by MS, but only for new ones so it's applied at creation time. Regrettably there's no "don't display zeros for existing workbooks/worksheets" option, and in first link you'd be led to the macro with shortcut within your personal book of macros, as I early posted.

Regards!
 
You must log in or register to reply here.
Back
Top