Forum Discussion

Whys1's avatar
Whys1
Copper Contributor
Sep 24, 2022

Restricting Excel File "Save As" Feature

Is there a way to limit the number of times an Excel file can be "Saved As"? With VBA?

I don't want to keep the Saved As feature from working at all, just limit it to work twice for any particular user. No more endless copies!

excel
Macros and VBA

4 Replies

  • GeorgieAnne's avatar
    GeorgieAnne
    Iron Contributor
    Sep 24, 2022
    Hello Whys1

    There is a Workbook_BeforeSave event that you can use.

    The first time you Save As you decrement a value by 1 until it gets to zero, and after that it will stop saving unless you do something special.

    I am not sure if you really want to do something like that, saving a file is kinda important.

    Maybe a better solution, albeit a bit more complicated is to use the Workbook_BeforeSave to judge how many minutes/hours has it been since the last save as and judge if you want to save as with a new name or not.

    And as I was writing this I though of a simpler solution that would use the Workbook_BeforeSave to add a timestamp to the file name and then just go and delete the files that are too close.

    So, the moral of the story here is that if you would tell us why is this needed, maybe we can get you a spot on solution.
    Georgie Anne
    • Whys1's avatar
      Whys1
      Copper Contributor
      Sep 24, 2022

      Hi GeorgieAnne

       

      Thanks for your response. I sell an Excel based planning tool for individuals, but lots of professional firms have been buying it and making endless free copies for clients. Not fair to paying customers. Individuals sometimes make a couple of copies to try out different scenarios, but don't need to "save as" more than twice. I want to limit the number of times the file can be "saved as" (not "saved") to prevent firms from taking advantage. It's okay for the file to be "saved" as many times as needed, just not using "saved as".

      This would not be a perfect fix but should act as a deterrent.

      • GeorgieAnne's avatar
        GeorgieAnne
        Iron Contributor
        Sep 24, 2022
        OK Whys1
        Thanks for the explanation, now am I entitled for a free copy just to see your handy work? 😉
        Then I would suggest looking into making your workbook an Add-In look here:
        https://support.microsoft.com/en-us/office/save-a-workbook-in-another-file-format-6a16c862-4a36-48f9-a300-c2ca0065286e
        https://www.contextures.com/exceladdins.html.

        Also the you can use the aforementioned event to pop up a message stating your wishes for them not to use the workbook in a way that would infringe on you, possibly send an email to you every time the client does make a new copy. That might be a bit intrusive so seek legal advice. Also you can make the event save a copy and delete all others. I mean there are many ways to do what you want, just how strict you want to be is the ultimate question.