VBA Shared workbook and Unshared workbook

Question

I tried to find the code to share workbook and unshared it with visual basic but I didn't find it, anyone know if its possible?

Another thing is shared workbooks when saved, update the workbook to all users... the question is if I save it with visual basic code the workbook will update to another users?

I am coding an button that when clicked it (share the workbook > fill the cells > save and unshared it).

Answer 1

I certainly agree with pnuts and the link he provided: Shared Workbooks are horrible.

To respond to the question though, if you record a macro in Excel you will see code like the following when you share a workbook.

Sub Macro1()
    Workbooks.Add
    With ActiveWorkbook
        .KeepChangeHistory = True
        .ChangeHistoryDuration = 30
    End With
    ActiveWorkbook.SaveAs Filename:= _
        "F:\Documents and Settings\student\My Documents\Book1.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, AccessMode:=xlShared
    ActiveWorkbook.ExclusiveAccess
End Sub

(If you don't already know how to record a macro in Excel then I recommend that you take the time to find out - it is extremely useful, particularly when you are just starting with VBA.)

If you copy this code into the VB Editor and click on certain words (SaveAs in particular) and press F1 you will get into the Help system.

From this recorded macro I surmise that removing Shared from a workbook is just a case of using SaveAs with an AccessMode other than xlShared (or omitted). After all, this is the dialog/option that appears when we manually share or un-share a workbook.

But, to emphasize, I am not advocating the use of Shared Workbooks.