[Solved] VBA: turn sharing off and on with VBA code

  • Does anyone know of a way to turn workbook sharing off and on using VBA? I have a spreadsheet that runs a macro perfectly except when the workbook is shared. Is there a way to simply toggle the sharing function OFF before the meat of the code runs, then turn it back ON at the end? I'm using Excel 97.

    Thanks! :cool:

  • Hi joldham,

    Try this two routines in a text workbook.



  • If you use this approach be mindful of the implications on other users:
    "The ExclusiveAccess method saves any changes you've made to the workbook and requires other users who have the workbook open to save their changes to a different file."
    I assume the macro wouldn't need exclusive access for too long. I am unclear if other users would have to "save changes to another workbook" only during the macro life-span or afterwards also.

  • Another simple and powerful approach is to call the built-in Dialog routine already programmed into Excel. The following statement gives you a lot of power to control virtually any aspect of file sharing between users of your workbook. This statement can be attached to a command button or in any part of your VBA code as it processes:


    Hope this helps.


  • Andy's code below works great. I had a bit of trouble turning the sharing back on with the SaveAs command, only to find out it was saving a copy on my root directory in Excel! All I needed to do was to specify the exact file location and it works great.

    Also, for my application, I'm using the shared workbook feature to only track and approve changes. There would not be multiple users of this worksheet unless someone tried to submit an update, which they are already prompted they cannot do.

    Thanks for the help! :spin:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!