Programming Excel with VBA and .NET

Program with Shared Workbooks

Once you share a workbook, any Visual Basic project it contains is no longer accessible. Excel can’t deal with multiple users editing the same macros, so it simply prevents changes to those macros. You can’t record new macros, either. However, you can run macros from shared workbooks.

Use the SaveAs method to share a workbook from within code. For example, the following code saves the active workbook for sharing:

Sub SaveAsShared(  )
    ActiveWorkbook.SaveAs , , , , , , xlShared
End Sub

Warning

Once you share a workbook, you can no longer edit the macros it contains. The macros still exist and they can still run; you just can’t change them. That’s because Excel doesn’t support shared editing in Visual Basic. To edit the macros, remove sharing.

To remove sharing, use the ExclusiveAccess method:

Sub RemoveSharing(  )
    If ThisWorkbook.MultiUserEditing Then _
      ThisWorkbook.ExclusiveAccess
End Sub

Removing sharing in this way erases change history and prevents other users who currently have the file open from saving their changes to the file. An alternate, kinder way to remove sharing is to save the workbook as a new file with the xlExclusive setting as shown here:

Sub SaveCopyAs(  )
    fil = ThisWorkbook.Path & "\" & "Copy of " & _
      ThisWorkbook.Name
    ThisWorkbook.SaveAs fil, , , , , , xlExclusive
End Sub

You can’t remove sharing in this way without renaming the file. The SaveAs method doesn’t change the access mode if you don’t specify a new filename.

When you save ...