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

joldham · Sep 24th 2003

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:

**Andy Pope** · Sep 24th 2003

Hi joldham,

Try this two routines in a text workbook.

Code

Sub MakeExclusive()


    If ActiveWorkbook.MultiUserEditing Then
        Application.DisplayAlerts = False
        ActiveWorkbook.ExclusiveAccess
        Application.DisplayAlerts = True
        MsgBox "Now Exclusive"
    End If


End Sub


Sub MakeShared()


    If Not ActiveWorkbook.MultiUserEditing Then
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs ActiveWorkbook.Name, accessmode:=xlShared
        Application.DisplayAlerts = True
        MsgBox "Now Shared"
    End If


End Sub

Display More

Cheers
Andy

Egad · Sep 24th 2003

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.

steinj · Sep 24th 2003

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:

Application.Dialogs(xlDialogFileSharing).Show

Hope this helps.

Jim

joldham · Sep 24th 2003

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!

Share