Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Disable Save in Template (Excel 2000)

    Hi, ... I'm in a regulated environment. I have created a template for my users. Because of the regulation, my users are not allowed to save the document at all, they can fill it out and print it only. Can the Save feature be disabled in the template. (I do need to update the template occassionally, so I would like to Save the template itself, just not the workbook it creates). Any clues would be appreciated. Thanks,
    --cat

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Disable Save in Template (Excel 2000)

    When saving the template set a password to modify and recommend Read-Only. That should enable password holders only (yourself) amend the document but prevent saving by others.

    To disbale SaveAs include the following code in the ThisworkBook module : <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = True Then
    Cancel = True
    End If
    End Sub</pre>

    Also include the following to prevent Excel prompting to save when the workbook is closed :<pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Me.Saved = True
    End Sub</pre>

    Andrew C

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Save in Template (Excel 2000)

    Andrew's suggestions will not prevent a user from saving once the template is opened with macros disabled.

    To make it more difficult for a user to save, hide the relevant sheets of the template and keep one visible, which tells the user that unless he/she opens the sheet with macros enabled, nothing can be done with it. Then protect the workbook's structure (Tools, protection, workbook).

    Then use the opening code to unprotect the workbook, unhide the sheets they need to see and print and use the code samples Andrew suggested to prevent them from saving.
    Don't forget tp protect the workbook again before your code is done, or they will be able to copy sheets out of your workbook into a new one and then save that....

    Of course the VBA literate users might access the code and still get away with saving, so you would have to hide the VBA project from view as well.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Save in Template (Excel 2000)

    Thanks, both of you. I will let you know --cat

Posting Permissions

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