Dear Excel friends,
I've come across a solution to an xlveryhidden problem, and in the process came upon a way to do an AFTERSAVE procedure.
Those who want to use the xlveryhidden designation are usually concerned with protecting their workbook data from unauthorized eyes.
There are several procedures explained on this board for xlveryhidding all files (except one) Before the Close of the Workbook. (thanks to many, especially Ritchie, who have helped me.)
The problem with using an xlveryhidden routine only upon the Close of the Workbook is that the workbook can be Saved without it's being closed. When the workbook is Closed all is hidden. But when the workbook is Saved without Closing, the workbook is saved with all the visible sheets visible, and those are the sheets that one is usually interested in keeping hidden. Thus someone can Save the workbook, and then Close without Saving, and the result will be that the workbook is saved with all the data that is desired to be hidden left as visible.
A solution would be to use a xlveryhidden routine also upon the saving of the workbook. This however, presents another problem, in that when one is working on a workbook one may want to do a save to protect any changes already made and yet still want to continue working.
If you add an xlveryhidden routine to the BeforeSave procedure the result will be that yes all worksheets will be hidden when they are saved, but you will be left with the workbook technically open, but all sheets hidden except your "warning -- enable macros" worksheet. You won't be able to continue working since all the worksheets will be hidden.
If you try to combine both the xlveryhiden routine and a 'unhide' routine in the same procedure, you'll find that this doesn't work either, since what you'll be doing is 1) hiding all (less 1), 2) saving with the sheets hidden, and then 3) un-hiding. Since all of these 3 routines are in the BEFORESAVE routine, what'll you'll discover is that yes, you will initiallly save the workbook with all sheets hidden. But then you will unhide them all again, so that you can continue working. If you Close at that point without saving you'll discover that rather than finding that the workbook was saved with your sheets hidden, you'll find that it will have been saved as 'all visible.' Why, how ??
The answer is that the hiding, saving, and unhiding were all done in the BEFORESAVE routine, which means that you saved with the sheets hidden, then unhid them all, and then exited the BEFORESAVE routine. At this point Excel says, "I was supposed to do all of the above BEFORE I saved, and now that I've finished all the BeforeSave activity (hiding, saving, and unhiding) I need to Save again, since the workbook is changed since it was last saved." (The previous time it was saved it was with the sheets hidden, and now after the unhiding, I need to Save again, since the workbook is different than it was at the previous save.)
The trick to making this work the way you want (saving the wb with the sheets hidden, but having them unhidden to allow further work) is to add a
ThisWorkbook.Saved = True statement just before exiting the BeforeSave routine.
This tricks the BeforeSave routine into NOT Saving again after having done all of the hiding/unhiding activity. This sets the "is the workbook saved" flag to "yes" or "true" and thus when Excel finishes doing all of the BeforeSave activity, and gets to the point where Excel would normally Save, Excel decides not to save again, since it's indicator says that the wb is already saved.
Thus the way to do a 'save as hidden' yet 'leave unhidden for further work' routine is to have the BeforeSave routine reference another module that: 1) hides all ws except one; 2) forces a Save with 'ThisWorkbook.Save', 3) unhides all the ws, and then 4) finally exists with a 'This Workbook.Saved = True" statement.
This solution came to me around 3:00 am (when the best ideas come after letting the brain work, while the body rests). Unless I've missed something basic I think it should work for others as well.
In addition, in researching this problem on this board I came upon people's desire for an AfterSave procedure similar to the BeforeSave procedure that Excel already has.
I think that this approach allows for AfterSave routines in the following manner: Have the BeforeSave procedure reference another Module.
In that module do all that you want to do Before saving. Then force a Save with 'ThisWorkbook.Save' Then do all that you want to do AfterSaving, and finally end with a "ThisWorkbook.Saved = True" statement.
Please comment and correct as is necessary. Sorry for the long post but I thought my insights might be helpful to others.
Thanks to Tom and Richie, for all the help that you have (knowingly and un-knowingly) given me via your posts on this board.
Stan Sz
I've come across a solution to an xlveryhidden problem, and in the process came upon a way to do an AFTERSAVE procedure.
Those who want to use the xlveryhidden designation are usually concerned with protecting their workbook data from unauthorized eyes.
There are several procedures explained on this board for xlveryhidding all files (except one) Before the Close of the Workbook. (thanks to many, especially Ritchie, who have helped me.)
The problem with using an xlveryhidden routine only upon the Close of the Workbook is that the workbook can be Saved without it's being closed. When the workbook is Closed all is hidden. But when the workbook is Saved without Closing, the workbook is saved with all the visible sheets visible, and those are the sheets that one is usually interested in keeping hidden. Thus someone can Save the workbook, and then Close without Saving, and the result will be that the workbook is saved with all the data that is desired to be hidden left as visible.
A solution would be to use a xlveryhidden routine also upon the saving of the workbook. This however, presents another problem, in that when one is working on a workbook one may want to do a save to protect any changes already made and yet still want to continue working.
If you add an xlveryhidden routine to the BeforeSave procedure the result will be that yes all worksheets will be hidden when they are saved, but you will be left with the workbook technically open, but all sheets hidden except your "warning -- enable macros" worksheet. You won't be able to continue working since all the worksheets will be hidden.
If you try to combine both the xlveryhiden routine and a 'unhide' routine in the same procedure, you'll find that this doesn't work either, since what you'll be doing is 1) hiding all (less 1), 2) saving with the sheets hidden, and then 3) un-hiding. Since all of these 3 routines are in the BEFORESAVE routine, what'll you'll discover is that yes, you will initiallly save the workbook with all sheets hidden. But then you will unhide them all again, so that you can continue working. If you Close at that point without saving you'll discover that rather than finding that the workbook was saved with your sheets hidden, you'll find that it will have been saved as 'all visible.' Why, how ??
The answer is that the hiding, saving, and unhiding were all done in the BEFORESAVE routine, which means that you saved with the sheets hidden, then unhid them all, and then exited the BEFORESAVE routine. At this point Excel says, "I was supposed to do all of the above BEFORE I saved, and now that I've finished all the BeforeSave activity (hiding, saving, and unhiding) I need to Save again, since the workbook is changed since it was last saved." (The previous time it was saved it was with the sheets hidden, and now after the unhiding, I need to Save again, since the workbook is different than it was at the previous save.)
The trick to making this work the way you want (saving the wb with the sheets hidden, but having them unhidden to allow further work) is to add a
ThisWorkbook.Saved = True statement just before exiting the BeforeSave routine.
This tricks the BeforeSave routine into NOT Saving again after having done all of the hiding/unhiding activity. This sets the "is the workbook saved" flag to "yes" or "true" and thus when Excel finishes doing all of the BeforeSave activity, and gets to the point where Excel would normally Save, Excel decides not to save again, since it's indicator says that the wb is already saved.
Thus the way to do a 'save as hidden' yet 'leave unhidden for further work' routine is to have the BeforeSave routine reference another module that: 1) hides all ws except one; 2) forces a Save with 'ThisWorkbook.Save', 3) unhides all the ws, and then 4) finally exists with a 'This Workbook.Saved = True" statement.
This solution came to me around 3:00 am (when the best ideas come after letting the brain work, while the body rests). Unless I've missed something basic I think it should work for others as well.
In addition, in researching this problem on this board I came upon people's desire for an AfterSave procedure similar to the BeforeSave procedure that Excel already has.
I think that this approach allows for AfterSave routines in the following manner: Have the BeforeSave procedure reference another Module.
In that module do all that you want to do Before saving. Then force a Save with 'ThisWorkbook.Save' Then do all that you want to do AfterSaving, and finally end with a "ThisWorkbook.Saved = True" statement.
Please comment and correct as is necessary. Sorry for the long post but I thought my insights might be helpful to others.
Thanks to Tom and Richie, for all the help that you have (knowingly and un-knowingly) given me via your posts on this board.
Stan Sz
