Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workbook_BeforeSave macro (Excel 97 SR2)

    I know Excel is NOT secure. My task is to make a certain workbook as hard to "break" as possible. I will be making the VBA password protected. I am using the code Servando posted in Post #361633.

    I want to have the workbook always saved with only the
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    I have a similar scenario. My solution is to set the Workbook_Open() as follows

    <pre>Private Sub Workbook_Open()
    DisplayInput
    End Sub</pre>


    DisplayInput is a macro that makes the various sheets visible.

    Workbook_BeforeSave() hides the various sheets and then runs the DisplayInput routine by adding the following line of code

    <code>Application.OnTime Now, "DisplayInput"</code>

    This causes the file to be saved before the DisplayInput routine is run.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    You can do that using code like the following (untested) code:

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim oSH As Object
    Dim strFN As Variant
    For Each oSH In Sheets
    If oSH.Name <> "Greetings" Then
    oSH.Visible = xlVeryHidden
    End If
    Next oSH
    Cancel = True
    If SaveAsUI Then
    strFN = Application.GetSaveAsFilename
    If strFN = False Then Exit Sub
    ActiveWorkbook.SaveAs Filename:=strFN
    Else
    ActiveWorkbook.Save
    End If
    For Each oSH In Sheets
    oSH.Visible = True
    Next oSH
    End Sub
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    Thanks Tony and Legare.
    I will try both methods.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    Legare,
    I could not get your macro to work. I tried the idea Tony suggested and it worked, but it I am still working on the technique to make the active sheet the one the user was on when he saved.

    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    Legare,
    When I used the macro you suggested, the workbook was saved with all the sheets visible (not desirable) and the user was left on the Greetings sheet. I am sure it was something I did wrong. When I tried the approach Tony suggested, the saved workbook had all the sheets xlVeryHidden except Greetings (as desired). My original attempt at his approach also left the user looking at the Greetings page. I have since gotten the macro to bring the user back to the sheet the user was on when they saved it, so the objective was reached. I am curoius as to why my statement "Application.ScreenUpdating = False", at the beginning of the code does not have the desired effect. I guess it does not matter that much, I am just suprised as I have been able to turn off the ScreenUpdating before. I do thank you for the response, and when the "crunch" is over, I will go back to see what I did wrong in implementing your macro. It looked like it should work!

    Thanks again,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    What didn't work. I warned that I had not tested the code, so it might not be perfect. You didn't give me much to go on to help.

    To keep the same sheet active:

    <pre>DIM oSH as object
    Set oSH = ActiveSheet
    </pre>


    At the beginning of the code.

    <pre> oSH.Activate
    </pre>


    at the end of the code.
    Legare Coleman

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    Can you show me the exact code you used? The one thing that I can of that would cause that problem would be if this line were left out or entered incorrectly:

    <pre> Cancel = True
    </pre>

    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    Legare,
    I am sending you a workbook with the macro I used. I pasted the macro in ThisWorkbook. Then I rem'd it out and saved the workbook and closed it. I opened the workbook, uncommented the macro and did a save (to test the macro). I closed the workbook and reopened it with macros disabled. The sheets were all visible. I closed the workbook.
    Now it gets interesting!
    I opened the workbook with macros enabled and went to the VBA editor. Your macro was STILL commented out! I figured it saved the file somewhere else so I did a search with Windows Explorer. I could find only one file.
    This one is WAY beyond me!

    The Workbook_BeforeSave that I got to work is below yours and is still rem'd out from when I tested yours.

    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    OK, I did find a couple of problems in my code. Try the attached.
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    Legare,
    A thing of beauty! Not only does it work but yours is successful in stopping the screen update! The user will probably never know it was saved with sheets hidden. I thank you. I will be using this "new and improved" version you have provided.

    Thanks again,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    Not quite a thing of beauty. I just took my dogs for a walk, and while walking realized that there is still one problem in that macro. If the user does a SaveAs and cancels the save, then he is returned to the workbook with the sheets hidden. The attached corrects that problem.
    Legare Coleman

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    Can't have been a very long walk! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_BeforeSave macro (Excel 97 SR2)

    Just long enough? <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •