Workbook_AfterSave

    Hi all,


    I have been using the following code as an after save event.


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


    Application.EnableEvents = False
    Application.ScreenUpdating = False


    'run code to hide sheets, columns etc.


    ThisWorkbook.Save


    'run code to redisplay sheets etc.


    Application.ScreenUpdating = True
    Application.EnableEvents = True


    Cancel = True 'cancel original save request


    End Sub


    Does anyone know of another or better way to achieve this?


    Can anyone suggest any scenerio where this will fail or get an error?

    What are you trying to do?
    turn off updating & events, then save, then turn on updating/events & cancel the save?

    Hi Egad,


    yes, have to disable events because iam forcing a new save with ThisWorkbook.Save and cant have recursive calls to my code.


    the cancel = true negates the original save request, otherwise the workbook will be saved again with my sheets un hidden.


    It works for me, just wondering if anyone has a better idea?

    Hi,


    Just to reiterate what Doug asked, what are you trying to do?


    You've outlined what your routine actually does but not the rationale for doing it. What is your purpose in using the code? For example, is its purpose to prevent the user from making a copy of your workbook that has all of the sheets displayed? If we know why you are doing it then we may be able to offer some constructive comments ;)

    Hi Richie,


    Ok I need to make sure that only certain users can see secure sheets, but other users can see general sheets.


    I have Open events to unhide sheets for a valid user.


    But, I cant inhibit a user from opening the workbook with Macros disabled so I have to ensure that whenever the file is saved the secure sheets are hidden.


    I dont want to force a save with the close event and hide sheets in case the user does not want to save his changes.


    So; I need to hide stuff - save - unhide stuff - user continues his work - all invisible to the user.


    Hope this makes it clearer.

    Hi Insomniac,


    Unfortunately Excel is not a very security-conscious environment. You will not be able to prevent your users from viewing the sheets if they have a reasonable VBA knowledge.


    In your particular example, if the user opens the workbook with macros disabled it only takes a few lines of code from another workbook to unhide your (very?) hidden sheets and they can then save the file without any restrictions.


    I'm tempted to post the code just so that you can see how easy it is but I'll check with a Moderator first as it may not be appreciated (because, of course, if the users haven't even heard of VBA then you're pretty safe ;) ).


    Anyway, in conclusion, I think your approach is fine provided your users are not likely to have any VBA skills (and, to be honest, not many people have a clue so you're probably safe).


    Hi Insomniac,


    Sounds like fun ;) Just a few quick suggestions.


    Instead of having the secure sheets in the same workbook why not remove them to a seperate linked workbook?


    I have a similar requirement where only certain people are allowed to see some confidential sheets. So instead of lumping it all into one file I made a seperate one with the confidential sheets linked to the general one. The confidential one opens at the same time as the general one but it's properties are set as an addin, this means that it's not visible unless a user overrides the addin setting through the VBE. To this end I have a control (button) in the general file that when clicked prompts for a password, if the correct password is entered then it triggers code to change the confidential file from addin true to addin false.....hey presto the magic of it all !! When finished the manager just clicks a button in the confidential file and the reverse takes place..it becomes an addin again with all the usual saves etc.


    Just some thoughts on an alternative approach, hope it gets your grey matter racing.


    AJW

    Insomniac,


    I draged my code across to an example of the above.


    There are two file that need to be place into the same folder. I only know how to attach one file to a post at a time so one here and one below.


    The idea is that your users would have access to the General File to do their work.


    If a Manager or someone higher in the food chain than us Planktonites needed access to the master data that the general file linked to they could click on the Open Confidential File button > Enter the Secret password and bang the file loads and opens. When they have finished with the confidential file they can escape it or hide it with the close file, x or clicking on the Close Confidential File button. The file will automatically save and revert back to an addin so that anyone who tries to view it by loading from the explorer window will see nothing.


    Granted if they have some VBA skill they can force it to be viewable, but most general users wouldn't know this. You could also mess around with hidden and very hidden for the sheets in question so that even if they do manage to force it open by changing addin to false they then need to force the hidden settings for the sheets in question. The trick with security is to make it as difficult as possible and multilayering your security does this reasonably well.


    BTW the Secret password for the password prompt and the VBE viewing is Secret.


    Have fun


    AJW

    Thanx all for replies,


    However Excel Workbook security was not actually what I was asking about.


    I probably gave a bad example.


    Sometimes I want some code to run straight after a workbook has been saved for a variety of different reasons.


    So I have been looking for ways to simulate an AFTER SAVE event.


    Secuity is not neccessarily an issue and I do not want to use special Buttons, passwords etc. The users of these files are only data entry people with no other Excel knowledge, it needs to look as standard as possible.


    Another example:


    10 chidren each take turns opening a quiz workbook and entering answers on their own sheet.So we dont want them copying answers.They save as they go, each time their sheet is hidden before save and unhidden after save to continue.
    When the teacher opens the workbook all sheets are made visible to check answers.


    The teacher also knows very little about Excel and definately nothing about VBA.


    AJW - I checked out your sample workbooks and some interesting variations there on what I normally do. As all say there is no real security in Excel and I do appreciate that.

    Thanx so much Insomniac!


    I came from the future (2019 to 2003.. hehe) and I can tell you that your code saved!
    Excel now has an unknown bug that, in some cases, prevent AfterSave event from firing and it's not related with Application.EnableEvents = False.
    The only missing point in your code is ThisWorkbook.Saved = True rightbefore End Sub to prevent an infinit loop if the workbook is being saved after a closing call.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login