How can I make it so only certain users' changes are saved and everyone else's are discarded?

Question

I have an excel database that we use to store product information. The admin of the sheet is the only person who should be able to edit the information on the sheet, which is easy to do through protecting the workbook/sheet.

There are macros in the sheet created to help streamline processes for the end users such as exporting a list of parts they need. So, while the workbook is open, the user needs to be able to make "edits" but these changes should not be saved (currently have a macro at workbook open that would reset any of these changes).

Because of the macros, this sheet is used through our OneDrive client but we are running into the issue of the duplicate files with the comp name.

How can I make it so only certain users' (the admins) changes are saved and everyone else's are discarded?

I'd like to prevent users from having to save a new copy each time they want to access the workbook and I'd prefer to not have to go to the document library and delete any duplicates every day. The perfect workflow for end users is to open the workbook from their OneDrive, get the information they need, and close it without interfering with anything an admin does.

Options I've considered but not sure if they would accomplish what I fully want:

• Have the admin check out the workbook when it's being updated (admin will sometimes have the workbook open all day so can users still access the last synced version with it checked out?)
• Set everyone's (except admins) permissions to view only in SharePoint (can the users still manipulate the sheet to get the full functionality and then it will not let it save those changes?)

Answer 1

This is how I would do it:

1. Provision a user group that can only add items at the site or site collection level. (Creators) Caveat: you may need to create a new permissions level.
2. Then create another group that contains all the admins you want to be able to change items. ( List Admins) I would assign at least OOB Contribute permissions to them.
3. Break permissions inheritance on the particular library - if necessary - (By the way, you probably Will need to do so).
4. Turn on document versioning on the list and require check outs.

This will solve your basic issues outlined in your question.

A humble suggestion:

I do not know what the usage of this document library is, but it sounds like this is a single document in a single document library.

If that is NOT the case, i.e.: this particular Excel file is mixed in with other documents: I highly recommend that you create a new Document Library and place this particular file in there and follow the steps above.

Also, to future-proof: import the excel file into SharePoint as a list (be sure to assign data types to the columns within Excel prior to doing so - saves A LOT of time). If you want to get extra cool points - for items that should be a drop down or multi select field, create a separate list that would act as a lookup list.

To replace the Macros use SharePoint Workflows (if on premise) or Flow (if in O365). Whatever you are automating with the Macros can be replicated if you use this path along with the future-proofing mechanism mentioned above.

Please mark this as an answer if it substantially meets your needs.