Enable Macro/ EnableEvents problem to protect sheets

[cadence]

Hello Can anyone help me:
I am trying to crack away to protect my workbook, by forcing users the enable macros on launching.

What I am trying to do is if Workbook_BeforeSave runs a routine called DoIt. This calls HideAll, saves, then calls ShowAll al whilst not updating. (ie so the user is non the wiser and the computer does not asked to save again if no changes are made).

The reason I am trying this is so that if the user closes the document, it will not ask them again to save. Instead it has preserved the state in the HideAll place (ie all important worksheets are VeryHidden.

It is only by enabling Macros on open can the ShowAll make them visible again.

Below is the code I am using and it works, except it seems the Application.EnableEvents = False in the DoIt macro does not seem to be doing what it should.

can anyone help show me what I am dong wrong?

very dearly love to get an answer as it is driving me up the wall!

Module 1:

Public bIsClosing As Boolean
Dim wsSheet As Worksheet

Sub HideAll()
Application.ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.Name = "Warning" Or wsSheet.Name = "Authorise" Then
wsSheet.Visible = xlSheetVisible
Else
wsSheet.Visible = xlSheetVeryHidden
End If
Next wsSheet
Application.ScreenUpdating = True
ThisWorkbook.Sheets("Warning").Activate
End Sub

Sub ShowAll()
Application.ScreenUpdating = False
bIsClosing = False
Dim wsDropList As Worksheet

For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.Name <> "Warning" Then
wsSheet.Visible = xlSheetVisible
End If
Next wsSheet

Sheet1.Visible = xlSheetVeryHidden
Sheet5.Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Budget").Activate
End Sub

This Workbook:

Private Sub Workbook_Open()
Call ShowAll
ThisWorkbook.Sheets("Budget").Activate
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call DoIt
Cancel = True
End Sub

Sub DoIt()
Application.EnableEvents = False
Call HideAll
ThisWorkbook.Save
Call ShowAll
Application.EnableEvents = True
End Sub

thanks so much
johnny

 

[cadence]

Erik, thanks the code look a bit cleaner.

I hadn't considered the the save as option, thanks for picking it up,
But would it be posible to allow a 'save as'?

later oon...
Its weird I have been testing this for a few hours and I keep getting varied results

Sometimes it does all that, then other times it does not do any of them.

Some times it just does not ask to enable and will either open visible or open dsable, but if it opens disabled and I press save it opens them all up fine.

It feels like it does not run the code it sees properley (if thats possible)

I already had my security set to medium, and have tried it on both a mac and pc platform and exactly the same thing happens,

I'm on office XP & Mac office 2004 and have been using your document.

I'm going to try it on another computer tomorrow, but welcome any suggestions.

 

[erik.van.geit]

did I understand correctly:
you were using the document I've sent to you and get strange results on 2 different machines ?
you can replicate the list of actions I displayed in my previous reply, but sometimes it doesn't work ?

Some times it just does not ask to enable and will either open visible or open dsable

I already told you that this is not normal and has nothing to do with the workbook itself

, but if it opens disabled and I press save it opens them all up fine.

not sure what you mean here

summarising
1. I cannot help when about that strange behaviour of enabling or not, but suspect this is also the cause of other problems (I must admit I can hardly or not believe that that settings would change that way by themselves)
2. perhaps you can describe what you did to get

then other times it does not do any of them

there is one possibility which disables all those features

Application.EnableEvents = False

when you test and get a bug, because a sheet doesn't exist, which under normal circumstances cannot occur, the "enableevents"setting could be left disabled