VBA Protect Shared Workbook with Track Changes Enabled and Password

[elmer007]

Hi,

This is my first post- please forgive/inform me of any mistakes.

I am trying to automate a repetitive reporting process. Before I can upload my reports, I have to protect a sheet, protect the workbook, and "Protect Shared Workbook." This last step brings up a box where I check "Sharing with track changes" and assign a password.

So far, I have successfully written macros for everything except the final step of protecting the shared workbook with a password for track changes. My many attempts at this last step have resulted in a variety of different failures and/or errors. Any help would be much appreciated!

The first half of the code does four things:
1) deletes a sheet called 'MasterListCopy'
2) makes a copy of the current 'MasterList' and names it 'MasterListCopy'
3) sets up conditional formatting to highlight any cells on the 'MasterList' that are not equal to their corresponding cell on the 'MasterListCopy' tab (used for bringing attention to worksheet updates)
4) hides the 'MasterListCopy' sheet

After these four things, the code for protecting the various aspects begins. I believe this is where the trouble lies...


Here is the code that I am working with now:

Sub Cond_Formatting_and_Protection()
'
' Cond_Formatting_and_Protection Macro
'
' Keyboard Shortcut: Ctrl+m
'
    If Not ActiveSheet.Name = "MasterList" Then
        MsgBox ("Macro: CTRL+m" & vbCrLf & "     -Set the conditional formatting and add protection to the sheet and workbook" & vbCrLf & vbCrLf & vbCrLf & "This is the wrong tab for this macro.  Run this from the 'MasterList' tab.")
        Exit Sub
    End If
    
    If Sheets("MasterList").ProtectContents = True Then
        MsgBox ("Cannot run this procedure when worksheet is already protected.")
        Exit Sub
    End If
    
    If MsgBox("Set the conditional formatting and protect the sheet and workbook?", vbYesNo, "Selection") = vbNo Then Exit Sub
    
    Application.DisplayAlerts = False
    
    On Error GoTo ErrHandler:
    Sheets("MasterListCopy").Visible = True
    Sheets("MasterListCopy").Select
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True

ErrHandler:
Resume AfterError:
AfterError:
    
    Application.DisplayAlerts = True
    
    Sheets("MasterList").Select
    Sheets("MasterList").Copy Before:=Sheets(3)
    ActiveSheet.Name = "MasterListCopy"
    Sheets("MasterList").Select
    
    Columns("C:AQ").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
        Formula1:="=MasterListCopy!C1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Sheets("MasterListCopy").Select
    ActiveWindow.SelectedSheets.Visible = False

    'I believe everything prior to this point works correctly
    'Next is where I try to protect the sheet, workbook, and protect and share

    
    Sheets("MasterList").Select
    ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:= _
        True, AllowFiltering:=True

    ActiveWorkbook.Protect Password:="mypassword", Structure:=True, Windows:=False

    ActiveWorkbook.ProtectSharing SharingPassword:="mypassword"
    
End Sub

I need the last portion of the code to be able to protect the shared workbook with track changes enabled and assign a password as the last thing that happens before the workbook is available to other users. Everything works fine without the very last line of code before the "end sub." When I put this back in, it doesn't work right.

Thanks to anyone that looks into it!

 

[elmer007]

*bump*

Does anyone know how to do this? Or is it not possible with VBA?

 

[tyger0951]

Workbook.ProtectSharing Method (Excel)

 

[elmer007]

Workbook.ProtectSharing Method (Excel)

Either I'm doing something wrong, or this doesn't work.

When I put in the following lines, I get problems:

Error, 'Object Required' with:

Workbook.ProtectSharing Password:="mypassword"

Error, 'Object Required' with:

Workbook.ProtectSharing SharingPassword:="mypassword"

Invalid procedure call or argument with:

ActiveWorkbook.ProtectSharing SharingPassword:="mypassword"

Or a pop-up asking me to save-as (that results in an error when I click 'no') with:

ActiveWorkbook.ProtectSharing Password:="mypassword"

 

[tyger0951]

More than likely something wrong with way implementing the code. The site states it saves as and protects. So with the last code does it work if you do save as?

Either I'm doing something wrong, or this doesn't work.

When I put in the following lines, I get problems:

Error, 'Object Required' with:

Workbook.ProtectSharing Password:="mypassword"

Error, 'Object Required' with:

Workbook.ProtectSharing SharingPassword:="mypassword"

Invalid procedure call or argument with:

ActiveWorkbook.ProtectSharing SharingPassword:="mypassword"

Or a pop-up asking me to save-as (that results in an error when I click 'no') with:

ActiveWorkbook.ProtectSharing Password:="mypassword"

 

[elmer007]

I have been researching every nook and cranny of the interwebs on this one, and I think it may be a bug in excel. If I remove the code to protect the structure of the workbook, then it works fine (except that I need to protect the structure, too). It seems that it can be done manually, just not in VBA.

I'm using Excel 2010.


Thanks to anyone that has looked into this. I'm afraid I'm going to have to use the mouse to make this task happen correctly.