Hi all,

Something tells me this is a Microsoft Bug and I'm SOL, but let's see if anyone see's something that I'm not.

I have workbook with protected sheets, that is also shared using a sharing password. I need to run macros to do various sorts. In order to run my macros, I simply have VBA unshare my workbook, unprotect the sheet, run my macro, reprotect the sheet, and then reprotect and share the workbook using "ProtectSharing". And it works! Sort of...

After running the macro the sheet appears to be [shared] based on the title bar, and my understanding is that the "ProtectSharing" method protects AND saves (though it didn't work when I added an additional "ActiveWorkbook.Save" line either). If I then follow this procedure below (exactly if you want to reproduce the problem), the sheet re-opens and is NOT shared!!!

1. run macro - sheet is shared and saved
2. close the worksheet but leave excel open
3. close excel
4. open excel
5. re-open the workbook using "Recent Workbooks"
6. File will open but is not shared anymore.

Here's the code I used:

Sub sortByDateRcvd()

'CHECK TO SEE IF OTHERS HAVE WORKBOOK OPEN, END SUB IF TRUE

users = ActiveWorkbook.UserStatus
If UBound(users, 1) > 1 Then
    MsgBox "Cannot Sort Workbook while other users have it open. Wait until other users have closed the workbook then try again."
    Exit Sub
    Else
End If

' UNSHARE THE WORKBOOK

If ActiveWorkbook.MultiUserEditing Then
    Application.DisplayAlerts = False
    ActiveWorkbook.UnprotectSharing SharingPassword:="DTL"
    Application.DisplayAlerts = True
End If

' TURN OFF SCREENUPDATING

Application.ScreenUpdating = False
 
'UNPROTECT THE SHEET
ActiveSheet.Unprotect Password:="DTL"

'SORT

    Range("entireSheet").Select
    Selection.Sort _
         Key1:=Range("E2"), _
         Order1:=xlAscending, _
         Key2:=Range("O2"), _
         Order2:=xlAscending, _
         Key2:=Range("B2"), _
         Order2:=xlAscending, _
         Header:=xlGuess, _
         OrderCustom:=1, _
         MatchCase:=False, _
         Orientation:=xlTopToBottom


'PROTECT THE SHEET
     ActiveSheet.Protect _
         Password:="DTL", _
         DrawingObjects:=True, _
         Contents:=True, _
         Scenarios:=True, _
         AllowFormattingCells:=True, _
         AllowFormattingColumns:=True, _
         AllowFormattingRows:=True

' TURN ON SCREENUPDATING

Application.ScreenUpdating = True

' RESHARE THE WORKBOOK

If Not ActiveWorkbook.MultiUserEditing Then
    Application.DisplayAlerts = False
        ActiveWorkbook.ProtectSharing SharingPassword:="DTL"
    Application.DisplayAlerts = True
End If

End Sub