Hi,

To protect some highly confidential data in a workbook I want to make
sure this data is always hidden and the worksheet protected before
saving so it is never visible in the saved version. So I wrote the
following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, ByRef
Cancel As Boolean)
' Check if the offending lines are visible. If not exit
If (shWork.Range("rCost").EntireRow.Hidden) Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

Call HideCosts ' Hide the offending lines
Call Me.Save ' Save the workbook
Call ShowCosts(True) ' Show the lines again
Me.Saved = True ' Showing the lines made the workbook
'unsaved' again

Application.EnableEvents = True
Application.ScreenUpdating = True
Cancel = True ' We already saved the workbook
End Sub

When I save the workbook using the 'Save' command (menu, toolbar,
programmatically) it works fine. The rows are hidden and the book is
protected. But if I want to close the workbook when it is not yet
saved, I get into a loop. Excel asks me if I want to save, I say yes
and the book is saved. Then it asks me again and again. When I say no
it simply closes but it will be very confusing to the people who'll be
using this workbook.

Has anybody an idea why I get in this loop. When the function exits,
the property 'Saved' is true, so I don't see why Excel keeps asking if
I want to save.
P.S. There is nothing in the Befor_Close event that changes anything
in the workbook.

Thanks in advance.

DQ

Try the following code in the thisworkbook module.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ChAction As Variant
If ThisWorkbook.Saved Then Exit Sub
ChAction = MsgBox("Do you want to save the changes you made to '"
& ThisWorkbook.Name & "'?", vbYesNoCancel + vbExclamation)
Select Case ChAction
Case vbCancel
Cancel = True
Case vbYes
SafeSave True
Case vbNo
ThisWorkbook.Saved = True
End Select
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI Then Exit Sub
SafeSave
Cancel = True
End Sub

Private Sub SafeSave(Optional bClose As Boolean = False)

'!!!your saving code here!!!

With Application
.EnableEvents = False
ThisWorkbook.Save
.EnableEvents = True
.ThisWorkbook.Saved = True
If bClose Then ThisWorkbook.Close False
End With
End Sub

By the way, you cannot secure highly confidential information in a
spreadsheet by this route as its pretty easy to unprotect sheets and
unhide cells. If it really is important that there is no unauthorised
access of the information then if you continue to use Excel you will
need to encrypt the confidential information as well.

Peter Grebenik