I have a Workbook with all the sheets protected which includes having the Gridlines and Headings turned off. I also has the complete workbook Protected - Structure so hidden sheets cannot be opened. When I save the workbook and close it, when reopened the gridlines and headings turn back on on all the protected sheets! I can see in the Formatting palette that the gridline and head boxes are now checked even though they are gray from the sheet protection.
How do I prevent this from happening??
Hi,
Just use the Workbook Open event in the Visual Basic Environment to turn them off when the workbook loads.
HTH
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I need a little more clarity in what the Workbook Open event in the Visual Basic Envirnoment. I thought Visual Basic was used to write Macros which is no longer supported in office 2008.Originally Posted by Richard Buttrey
Hi.
If you're an Apple Mac user and have moved up to Excel for Mac 2008 then you are correct.
In their infinite wisdom MS dropped Visual Basic from that release. Having realised they've shot themselves in the foot there are rumours that it will be re-introduced in some future edition but that's only a rumour.
If you are not using Excel for Mac then post back.
Regards
Yes, I am using the Mac 2008 version. And I do miss using Macros. So now everything has to be done with formulas that is far more difficult.
Indeed!
Although I'm a great fan of the Apple Mac platform and use it for for just about everything else in my life, when it comes to Excel it is a very poor second cousin. Not only does the lack of right clicks and the non standard Fn keys drive me insane, it is nowhere near as productive as the Windows version. Particularly in the Visual basic Environment, where not only is some of the syntax different and several instructions sets are missing, but the autocomplete and syntax checking is non existent as is the Watch window. As a developer it is far more difficult to work with.
[/HobbyHorse]
I agree with all you said, but do you a solution for my problem?
In short and without macros, no.
And I say that because I can't replicate your problem on my Excel for Mac 2008 version. It works exactly as I would expect. i.e the status of these setting is preserved.
Is this a workbook that you save and open across a network and if so are you sure no one is resetting the gridlines and headers before you next open it?
Regards
No the workbook is only on my computer and the only way I share it is by emailing it to someone. One more characteristic is it seems to happen when I have 2 windows open at the same time of the same workbook. One has one tab display the other has another.
Hi,
Ah, that's a critical new piece of information.
So what do you do when you save this two window workbook? Are you closing the no gridline/no headings window first and then saving the workbook, or are you saving the whole workbook from one of the windows then closing the workbook?
If the latter when you re-open the file you should still have the two windows open, one with grids/headings and one without. or at least that's what happens on my system.
It would seem that the solution is to first close the window that has the grids/headings then save the workbook.
HTH
Actually all the sheets have their gridlines and heading hidden as well as all the sheets are Protected. The problem only occurs if I Protect the whole Wookbook as well. I do the wookbook protection to keep a hidden tab hidden. It cannot find a particular pattern in what I am doing but when it does happen every sheet has it gridelines and headings turned back on, but the sheet remain protected. It never happens unless the wookbook is protected.
Hope this helps.
I found how to create the problem but do not know how to fix it. Here is the process.
Create a new workbook. Turn off the Gridlines and headings. Protect the sheet. Then under window select New window so you have 2 windows of the same workbook open. The new window will the gridlines turned back on. If you close the first window without the gridlines you are now stuck with a work book that will have all the gridlines and headings turn back on even though it is protect.
Does that help?
Protect the workbook, including Windows. Then users can't open a new window.
Or add code in the ThisWorkbook module to clear gridlines:
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Not being able to open another workbook takes away the feature of seeing what a data change does to the results. They need to be able to open two windows to do so.
The code you suggest, is that a macro? if so, Excel 2008 does not support macros, if not can you tell me in more detail where I enter this code?
Oops, no VBA in Excel 2008.
There may be a registry key (or the Mac's equivalent) to keep gridlines from appearing in a new window, but I have no idea where it would be.
Harlan Grove posted this a few years ago:
.Harlan Grove posted on Monday, August 27, 2007 3:25 AM
If you never want to see gridlines in new workbooks you create, and you're willing to risk editing the Windows Registry, use regedit to add 4000 hex to the value of Options3 in the subkey
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options
The 11.0 bit is specific to Excel 2003. Use 10.0 for Excel 2002, 9.0 for Excel 2000, 8.0 for Excel 97. Dunno whether this works for Excel 2007.
I keep a copy of the Toggle Gridlines button from the Forms toolbar in my menu, since I always turn off gridlines when I open someone else's workbook.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks