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

 

[erik.van.geit]

Hi,

Application.EnableEvents = False in the DoIt macro does not seem to be doing what it should.

what do you think it should do ?
for what purpose did you use that lines ?
are there events triggered while you do not want them to be run ?
from the code you posted this cannot be known

kind regards,
Erik

 

[cadence]

erik.van.geit

The purposeof the DoIt macro is so that everytime the file is saved it in the state of HideAll, ie important worksheets are VeryHidden,

second, Whilst doing this I also do not want the disturb the user, so it is returned to a state of ShowAll (the state the user has been working in).

The third part is that because ShowAll occurs after the save, I don't want the file to be updated, otherwise it will only ask it to save again if the file is close (with no user defined change)

currently it gets caught in a circular loop of save when you try to close it becasue it keeps updating the HideAll, Save, ShowAll routine.

what do you think it should do ?

not let excel think the state of the file has changed

for what purpose did you use that lines ?

so that when it closes it does not ask to save and save again

are there events triggered while you do not want them to be run ?

No, the DoIt routine shouuld only run when being saved.

from the code you posted this cannot be known

Does my explanation above help?

does this make sense can you help?

cheers

 

[erik.van.geit]

this works for me

in a normal module

Public closing As Boolean

add in the workbookmodule

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call HideAll
closing = True
ThisWorkbook.Save
closing = False
End Sub

best regards,
Erik

 

[cadence]

erik.van.geit
Thaks Erik, but what can I do if I don't want to force users to save if closing.

I only want it to save in the HideAll state, and the return to ShowAll without having to save again if no changes are made by the user?

Can you tell me the difference between these two?
Public closing As Boolean & Public bIsClosing As Boolean

cheers

 

[erik.van.geit]

Can you tell me the difference between these two?
Public closing As Boolean & Public bIsClosing As Boolean

"closing" is a name I chose for my variable
you can name it "banana" if you want

I only want it to save in the HideAll state, and the return to ShowAll without having to save again if no changes are made by the user?

you mean
when closing the workbook only save if needed
add a line on top of the beforeclose-event

if thisworkbook.saved then exit sub

 

[cadence]

Erik,
almost but it still does not leave user with the option to not save their changes.

on save can it not:
Go to HideAll
Save changes
Return to Showall (same state as before it went to hide all)
and then not need to be saved again if closing (ie it just closes in its current state and not save again on close)

***this is so excel does not think a change has occurred and will not ask for antoher save if you close it immediatly afterwards***

when closing the workbook only save if needed

No, I still want the user to not save if they don't like there added changes, because it was saved in the DoIt routine.

does this help explain myself better, is it feasible?

thanks

 

[erik.van.geit]

please confirm if I did understand the whole thing

on save:
hide sheets and save
show sheets again (not saved)

on close
if there are changes (apart from showing the sheets again) ask to save
if no changes just close


waiting for your reply, but in the mean time a little suggestion
adding (untested)

ThisWorkBook.Saved = True

at the end of ShowAll might well be your solution

EDIT:
only now I realize why you asked
Can you tell me the difference between these two?
Public closing As Boolean & Public bIsClosing As Boolean
didn't notice you had already a public variable

 

[cadence]

Yes, that is exactly what I'm after.

The main reasonis so each time the file is opened is starts in the hideAll state. this forces user (properley) to enable macros each time they open.

where would you suggest I insert the
ThisWorkBook.Saved = True

if you can solve this I will be so truly gratefull I have been inching forward for ever trying to get a solution that works.

 

[erik.van.geit]

try this
there may be some wrong details which I didn't understand (showing to hiding certain sheets) but the general functioning seems OK to me
(I must admit being tired of opening and closing 100+ times the file)

Option Explicit

Public bIsClosing As Boolean
Public bMadeChanges As Boolean

Sub HideAll()
Dim wsSheet As Worksheet

    With ThisWorkbook
    'you can better show at least one on top
    'whenever the taborder would change your code could bug
    'because hiding all sheets is impossible !!
    .Sheets("Warning").Visible = xlSheetVisible
    
        For Each wsSheet In .Worksheets
            If LCase(wsSheet.Name) = "warning" Or LCase(wsSheet.Name) = "authorise" Then
            wsSheet.Visible = xlSheetVisible
            Else
            wsSheet.Visible = xlSheetVeryHidden
            End If
        Next wsSheet

    .Sheets("Warning").Activate
    .Save
    
    End With

End Sub

Sub ShowAll()
Dim wsSheet As Worksheet

If bIsClosing Then Exit Sub

    With ThisWorkbook
    
        For Each wsSheet In .Worksheets
        wsSheet.Visible = xlSheetVisible
        Next wsSheet

    .Sheets("Warning").Visible = xlSheetVeryHidden
    .Sheets("Authorise").Visible = xlSheetVeryHidden
    'next line seems annoying to me
    'every time you save you and up on the budget-sheet
    '.Sheets("Budget").Activate
    
    End With
    
    bMadeChanges = False
    
End Sub

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
        .Save
        .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)
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