xlveryhidden - problem / solution; & AFTERSAVE routine f

[StanSz]

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

 

[parry]

Re: xlveryhidden - problem / solution; & AFTERSAVE routi

Hi, if your going to save the workbook then why dont you simply use the BeforeClose event?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call HideSheets
ThisWorkbook.Save
End Sub

Sub HideSheets()
Dim Sh As Worksheet, VisSht As String

'Name of the sheet you want visible
VisSht = "Sheet1"
Sheets(VisSht).Visible = True

For Each Sh In ThisWorkbook.Sheets
If Sh.Name <> VisSht Then Sh.Visible = xlSheetVeryHidden
Next Sh
End Sub

 

[StanSz]

Re: xlveryhidden - problem / solution; & AFTERSAVE routi

Parry,

BeforeClose hides ONLY IF you're closing. If you're Saving separately from closing, nothing will be hidden in the Save -- it will all be left visible in the saved workbook.

Stan Sz

 

[parry]

Re: xlveryhidden - problem / solution; & AFTERSAVE routi

Parry,

BeforeClose hides ONLY IF you're closing. If you're Saving separately from closing, nothing will be hidden in the Save -- it will all be left visible in the saved workbook.

Stan Sz

Naturally. I presume your objective is to ensure that when the book is opened again then only one sheet will be visible. The Open event is no good because the user could say no to enabling macros. If I make sheets visible, save the book then close it the BeforeClose event will still run.

 

[Colo]

Hi Stan Sz,
Just thought, can you use Application.OnTime method for what you would like to do?
(I've never tried it in my work as yet.)

'Place the following in ThisWorkbook Module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.OnTime Now, "CheckSavedProperty"
End Sub


'Place the following in a standard module
Sub CheckSavedProperty()
    Do: DoEvents:   Loop Until ThisWorkbook.Saved
    MsgBox "Saved"
End Sub

 

[StanSz]

Re: xlveryhidden - problem / solution; & AFTERSAVE routi

Colo,

Yes, I am using the ApplicationOnTime Now routine to call the Module which does the hiding, saving, unhiding.


Parry,

So, if I understand you correctly, the BeforeClose routine would still hide all the sheets, even if the wb had been previously saved. I guess this would even apply to a SaveAs. Yes ?

Maybe I was checking wrongly, but I thought I was finding my wb saved with sheets visible, even after closing.

Thanks for your ideas.

Stan Sz

 

[parry]

Re: xlveryhidden - problem / solution; & AFTERSAVE routi

Hi Stan, yes if you SaveAs then any code in the current workbook will copy over so the new workbook will now have the BeforeClose event that hides sheets. The problem with this though is that the act of hiding the sheets requires the book to be saved so if you want to close the workbook without saving it isnt possible with that code.

A solution maybe to use an Add-In which looks for a particular book name then hides the sheets when it opens it and saves the book at the time. So that would mean an Open event on the addin book. Even this isnt foolproof as someone could simply uninstall the addin. Its difficult to have a solution which will hide sheets but not force a save.

 

[StanSz]

Re: xlveryhidden - problem / solution; & AFTERSAVE routi

So, it sound like I could have saved myself a lot of work by not worrying about a hide/unhide routine for the BeforeSave procedure, since any Save, or SaveAs would be overwritten by the hidden sheets when the wb was closed.

But it works, now, and I'm not about to trying changing it, for fear of messing it up.

Anyways, I've found a way to do an 'AfterSave' routine.

Thanks

Stan Sz

 

[StanSz]

Re: xlveryhidden - problem / solution; & AFTERSAVE routi

Parry,

Just noticed your New Zealand flag -- great. I've got some money in New Zealand dollars -- up c 39% since Jan '03 (or more accurately said the US$ is down c 39% vs the NZ $ since Jan 03.)

Thanks for your help

StanSz

 

[StanSz]

Re: xlveryhidden - problem / solution; & AFTERSAVE routi

Parry,

Just to clarify further, I ended up doing the BeforeSave routine I described above because I had used a different BeforeClose procedure as devised by Richie (in order to avoid having the "warning" ss be visible upon close.)

Here's that BeforeClose Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans As Integer

If Not ThisWorkbook.Saved Then
ans = MsgBox(prompt:="Do you want to save the changes to " _
& ThisWorkbook.Name, Buttons:=vbInformation + vbYesNoCancel, _
Title:="Microsoft Excel2")
Select Case ans
Case Is = vbYes 'user wants to save
Module1.MacrosClose 'hide sheets and save workbook
Case Is = vbNo 'user doesn't want to save
ThisWorkbook.Saved = True 'don't save workbook
Case Is = vbCancel 'changed their mind so don't close
Cancel = True 'cancel the closing process
Exit Sub
End Select
End If

End Sub

Sub MacrosClose()
Dim sht As Worksheet

Application.ScreenUpdating = False
Sheet1.Visible = xlSheetVisible
'make sure the warning sheet is visible
'(use Codename in case user changes sheet name)
For Each sht In ThisWorkbook.Sheets
If sht.CodeName <> "Sheet1" Then sht.Visible = xlSheetVeryHidden
'hide all the other sheets
Next
ThisWorkbook.Save
'force a save of the workbook with sheets hidden
Application.ScreenUpdating = True

End Sub