Unable to UNSHARE a Workbook

[PosseJohn]

I have a test workbook that I was trying some new code.

The problem seems to occur when I try to save a SHARED workbook for EXCLUSIVE USE.

The following code is used in the Workbook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo ErrorHandler

    Select Case MsgBox("Do you want to save your work?", vbYesNoCancel)
    
        Case vbCancel
            
            Cancel = True
            
            Exit Sub
            
        Case vbNo
            
            ThisWorkbook.Saved = True
            
            Exit Sub
            
        Case vbYes
        
            Select Case MsgBox("Do you want to save as a SHARED workbook?", vbYesNo)
                
                Case vbNo
                
                    If ThisWorkbook.MultiUserEditing = True Then
                        ThisWorkbook.ExclusiveAccess    '<<<<<< THIS IS GIVING AN ERROR
                    Else
                        ThisWorkbook.SaveAs Filename:=ThisWorkbook.Name, FileFormat:=xlOpenXMLWorkbookMacroEnabled, AccessMode:=xlExclusive
                    End If
                
                Case vbYes
                
                    ThisWorkbook.ProtectSharing Filename:=ThisWorkbook.Name, FileFormat:=xlOpenXMLWorkbookMacroEnabled
                
            End Select
    
    End Select

    Exit Sub

ErrorHandler:

    MsgBox Error & vbCrLf & Err.Number

End Sub

As a 'test' workbook, there is no other code present.

This is being performed on a stand alone computer, no other users (testing at home for project at work).

Using Excel 2007, Windows Vista Ultimate

[ragulduy]

ExclusiveAccess only works if multiuserediting is true. You have the if statement for this before you use exclusiveacesss but then you say you are perfoming this on a stand alone computer with no other users.. Are you skipping the code to that line to test it or is multiuserediting really true when you are getting the error?

[PosseJohn]

I was trying this at home which was a standalone, no other computers.

I have now tried the same code at work, which is LAN connected, still receive the same error at the same line.

Also, I've noticed that I can't manually stop sharing. On the Share Workbook dialog, the "Allow changes by more than one user at the same time" is checked, but greyed out.

Excel won't release it from shared status.

Anymore help would be appreciated...

[Marcol]

This won't help for this situation, but .....

There are two kinds of shared workbooks, those that are corrupted, and those that soon will be.

Avoid shared workbooks, it's much safer to use Access if possible.