Limit user abilities

Occasional Contributor

We have created a SharePoint site for budgeting (oh what fun) but it will prevent the need to send files back and forth or setting up network locations to have the files stored.  When saving files to SharePoint, initially I was concerned about the users having the ability to delete files so we solved that with enhanced permissions.  Also, there was the concern of opening the file in the browser where the macros would not work (therefore the built in VBA coding for security of the file would not work), so we went into the SharePoint site and made it to where when they click on the file it will open in the Excel application.  So I THOUGHT my concerns were dealt with until one of the Managers told me what they did was that they DOWNLOADED their file and wanted to know how to UPLOAD it again.  Ideally, we do not want the users downloading as the file becomes "unsecure" on their computers. 

 

I have looked around and found where I could go into Site Collection Admin and activate Collection features and then make a copy of the view only permission and add the edit feature.  The problem is that when that happens the file will only open in the browser and the security VBA code does not work.

 

Ultimately need the users to be able to open in excel, modify the file, save the file but that is about it.  No checking in / out, no deleting files or versions, not even adding files to the site/libraries.  Really dont need them to be able to "Save as" in excel.

 

Thought about using the audit logs to show me who has modified files as under the custom it says you can see who downloaded but I downloaded a file, ran the audit report and I simply cannot "read" the log to see what it is talking about.  Was hoping I could see something that could say file name and "download".

 

Right now we are combatting this by simply bluffing to the users saying we are monitoring and if we see a file downloaded or checked out, we will lock that user out and if it becomes too wide spread we will lock the site to the point they will need to make appointments to review files with one of the site owners and work on their files at that time.

 

However, would like to solve this correctly by limiting access and understand that this may require additional coding in the excel file (perhaps to disable the save as) but looking for any ways to work this on the SharePoint side so we can have the restricted use like view but still open in excel.

 

Any assistance is greatly appreciated.

2 Replies
What about sharing the file using a different approach - one that allows you to block download? See: https://support.microsoft.com/en-us/office/block-downloads-for-view-only-files-in-sharepoint-and-one...
Tried and when tested, the problem here is that the file only opens in the on-line browser and the VBA/macros in the file (which hold the security for locking cells / tabs / etc) does not work. Was able to block the "Save as" feature which somewhat works... when they opt to download it opens and they can make changes but when they go to save as it tells them it is disabled. Now, they could simply save the file but not many here know that the file saves to the download folder and even if they do see it in the download folder unless they know how to change the file name by changing it in windows explorer, they cannot change the file name. Really by disabling the save as it makes downloading more trouble than its worth (to me at least). The code used:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If SaveAsUI Then
MsgBox "The 'Save As' function has been disabled." & _
Chr(10) & _
Chr(10) & "File location is to remain to be the SharePoint site" & _
Chr(10) & "Please select 'Save' to save the file, otherwise cancel. If a working or test copy of the file is needed, contact either the Controller or Asst. Controller", vbInformation, "Save As Disabled"
Cancel = True
End If

Still would be great to just disable the option to download but still have the file open and edit in excel if possible