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]

Wow that looks amazing, I will try give it a go and get back to you.

Thanks for putting so much effort in, Its really amazing.

I think I have tested this a 1000 times so I know exactly how you feel!

 

[erik.van.geit]

I think I have tested this a 1000 times so I know exactly how you feel!

once it's working we quickly forget the pain we had to get there
I'm curious if it works under all circumstances
couldn't find a problem yet

perhaps the code could be made simpler
after some time, it turns out to what I would call spaghetti-code

 

[cadence]

I have found a couple of small glitches:
1. IF you open with disable macros all the work books are visible (it should just be Warning & Authorise)

2. If I try and Run HideAll Manually I get an error message:
Run-time Error 1004 Method visible of object_worksheet failed
on

wsSheet.Visible = xlSheetVeryHidden

- which is weird because it works in my original version of HideAll.

so I'm not sure yet if it capturing the save in the HideAll State!

Thanks

PS: what state should I be testing your Save changes Dialog Box?

 

[erik.van.geit]

Hi,

I tested again: everything is fine for me.
Open the workbook, enable macros, save it, close it.
Now everyting should work.

if not then
feel free to email me your workbook (without data)
subject line: force enable macros
link to this thread within the message body

what do you mean here

what state should I be testing your Save changes Dialog Box?

 

[cadence]

Erik

Cool I will test it again, before I need to send it to you.

I've been working from a dummy book that I could test before I incorporated it into my document.

My question just asking if there was any particular condition I should test, but I am trying all variations.

thanks. (i'll get back to you)

 

[erik.van.geit]

got your workbook by email

I'm completely lost at the moment.
There is a line

.Save

in the hideall-macro
which is running but doesn't save

this is my test

    MsgBox ThisWorkbook.Saved
    .Save
    MsgBox ThisWorkbook.Saved

when closing I get "FALSE" twice

 

[erik.van.geit]

I was "sure" it worked before
perhaps I changed a line without testing again

it is clear that it won't save because the "before-save" is waiting to finish (if that's about clear)
since I know the cause by now, I will probably solve it

EDIT:
I thought it would take half an hour ... this works for me
see 2 lines with '''''''' at the end

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If bIsClosing Then Exit Sub

Dim response As Integer

    With ThisWorkbook

        If bMadeChanges Then
        response = MsgBox("Save Changes?", vbYesNoCancel, "SAVE")
        Else
        .Close False
        End If
    
        Select Case response
        
        Case vbYes
        bIsClosing = True
        bMadeChanges = False
        HideAll                 '''''''
        .Close False
        bIsClosing = False
            
        Case vbCancel
        Cancel = True
        
        Case vbNo
        .Saved = True
    
        End Select

    End With

End Sub

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

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If bIsClosing Then Exit Sub         ''''''''''
Call DoIt
Cancel = True
bMadeChanges = False
End Sub

Sub DoIt()

    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    Call HideAll
    Call ShowAll
    .EnableEvents = True
    .ScreenUpdating = True
    End With

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
bMadeChanges = True
End Sub

 

[cadence]

Erik,
I had a look at your changes and have ried to implement them.

but I am still facing some difficulties with getting it to work correctly.

1. everytime I open with macros disable it is stil showing all the worksheets.
or
2. sometimes it just opens with out asking for macors to be enables/disabled - and opens with just warning and authorise visible, but then if you do a save they all become visible.

Its weird it almost works as a way around marco protection!

there are acouple of otehr glitches, but might all get solved in one.

would it be possible to email me your workbook I can test to see if its me or universal? and I'll get back to you.

thanks.

 

[erik.van.geit]

2. sometimes it just opens with out asking for macors to be enables/disabled - and opens with just warning and authorise visible, but then if you do a save they all become visible.

this has really nothing to do with this particular workbook, this is even impossible without you changing some settings

I tested everything again

only open (and close) workbook
1. macros disabled: only sheet "macros disabled" visible
2. macros enabled: all sheets visible except "macros disabled"
repeat as much as wanted, still 1. & 2. working OK

open, enable macros, change, save & close: no message
still 1. & 2. working OK

open, enable macros, change, do NOT save & close: you get a message
A. click YES: file closes
open again changes are indeed saved: still 1. & 2. working OK
B. click NO: file closes
open again changes are not saved: still 1. & 2. working OK
C. click CANCEL
file is not closed
if afterwards you choose A or B everything is working as described

 

[erik.van.geit]

I've send you the file.

just now changed another thing:
if you do "Save As" there was "onlogic" behaviour" (nothing really bad, but I do not like that)

insert some line in the before save procedure if you want

    If SaveAsUI Then
    MsgBox "Sorry, you are not allowed to use Save As.", vbCritical, "No Save As"
    Cancel = True
    Exit Sub
    End If

result

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    If SaveAsUI Then
    MsgBox "Sorry, you are not allowed to use Save As.", vbCritical, "No Save As"
    Cancel = True
    Exit Sub
    End If

If bIsClosing Then Exit Sub
Call HideSaveShow
Cancel = True
bMadeChanges = False

End Sub