application.enable events problem

[cwunderlich]

Let me try to lay out what I am trying to do here:

-I have a password protected file which only certain ppl know the password to.
-once those ppl are in the file, a macro runs which unhides all the sheets. If an unauthorized person is viewing the wkbk (i.e. they open the wkbk with macros disabled), they can only view one sheet (not the sheets with the sensitive information)

-I am trying to make code which hides all sheets upon saving and also upon closing the wkbk. I have set up code which allows me to be able to SaveAs in other locations while still keeping all sheets hidden upon saving. It also allows the authorized user to click save (w/o closing) and it will save the wkbk but keep all sheets unhidden.

-I am also trying to get it to hide all sheets upon closing, when I click to close the file, I have the following code:

Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("CloseVariable").Range("CloseVar").Value = "True"
HideSheets

End Sub

The "CloseVariable" sheet is just a hidden sheet I have set up to track whether or not the user is trying to save with closing the file or save w/o closing. After this code is run, the excel event asking if I would like to save changes pops up. If I click NO, there is no problem, the file closes, the sheets are hidden and everything is fine.

However, if I click yes, the following code is run: (this is the code I mentioned earlier, used to hide sheets upon saving the file). It has if statements to handle the situation of whether or not it is a saveAs and whether or not it is a save w/closing or w/o closing the file. However, excel crashes when I get to the indicated line shown below. I am not sure I fully understand what is going on with the application.enable events code.

Any ideas of why excel is crashing on me??

Sub Workbook_BeforeSave(ByVal SaveasUI As Boolean, Cancel As Boolean)
HideSheets

If SaveasUI = False Then
Cancel = True
Application.EnableEvents = False
Else
Application.EnableEvents = True
End If

If Sheets("CloseVariable").Range("CloseVar").Value = "False" Then 'If the save is not part of a close, then unhide sheets

If SaveasUI = False Then 'If this is not a saveAs, then unhide sheets
ThisWorkbook.Save
UnhideSheets
End If
Else

Workbooks.Close <-----when the macro gets to this line, excel asks me AGAIN if I would like to save changes and then it crashes - (when it gets here, I would like it to just close the file, w/o asking again) - anyone know why this is occurring??

End If

Application.EnableEvents = True

End Sub


Thanks!!!

 

[cwunderlich]

bump