Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
Aug 27, 2020

Preventing document to be saved by other users?

I have created a daily "pass on" for supervisors to fill out per their request. This is a macro enabled document automated with buttons. The supervisors are not very tech savvy and continue to "file-save" document overwriting my original (I do have a backup but I really don't want to continuously reload the backup for every supervisor's shift, 3 shifts per day 7 days a week). I have a save as command in one of the VBA buttons, it is not necessary for the supervisors to save over my master copy. This is located on a network drive and they all have access to it.

 

Is it possible for me to restrict the supervisors access on the document to save. If they do a 'save as' I cannot prevent that but I am just wanting to eliminate them from 'File-Save'. They don't all seem to understand when I stress just to press the macro buttons, but they want it automated so the buttons must stay. They 'file-save' then press the macro buttons...facepalm.

 

Is there another way to go about this other than restricting access? Maybe a VBA to lock the document from 'file-save' without being a read only copy since the VBA buttons don't work with a read only version of the document. 

 

Below is the document. Advice is welcomed.

Need Help

2 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Aug 27, 2020

    You say this is a Macro-enabled workbook with buttons that run macros but the attachment is .xlsx which is not macro-enabled.  TBH I didn't even bother opening the attachment.  IF your actual document is macro enabled (.xlsm or .xlsb) then you can write a small event routine under the "ThisWorkbook" object:

     

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

     

      [enter some code to prevent save and/or warn before saving except if the save request is coming from your button]

     

    End Sub

     

  • mathetes's avatar
    mathetes
    Gold Contributor
    Aug 27, 2020

    It's not clear to me why you're even using Excel for this. It's just a format to collect information. There don't appear to be any calculations, or any of the other things one usually expects from an Excel workbook.

     

    Could Word not do the task of enabling these supervisors to enter the needed information and send it on?