Disable Save in Template (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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 :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = True Then
        Cancel = True
    End If
End Sub

Also include the following to prevent Excel prompting to save when the workbook is closed :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Me.Saved = True
End Sub

Andrew C

Reply | Quote

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.

Reply | Quote

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

Reply | Quote