Can protect worksheet then workbook but not Protect and Share in code

[ctmom]

Hi,
I am trying to pw-protect the sheet, the pw-protect workbook
and the pw-protect sharing in code. I can do this manually in Excel
but not in Excel VBA or Access VBA code. I can protect the
sheet/workbook and sheet/sharing combination but not all three. Users
can rename worksheets, etc, with the workbook unprotected.

Below is my code that gives a run-time error 5 on the
objXLBook.ProtectSharing line.

Sub SetSharing()
Dim strFilePathName As String
Dim objXLBook As Excel.Workbook

Set objXLBook = ActiveWorkbook
strFilePathName = "c:\New Microsoft Excel Worksheet.xls"

ActiveSheet.Protect Password:="Password", _
DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFiltering:=True
objXLBook.Protect Password:="Password"
objXLBook.ProtectSharing strFilePathName, , , , , "Password"

objXLBook.Save
Set objXLBook = Nothing
End Sub

While code is in break after the error on the objXLBook.ProtectSharing
line, I can go into Excel, click Tools, Protection, Protect and Share
Workbook, check off Sharing with track changes, enter a password twice
AND IT WORKS! Is this a bug that I cannot do this in code?

Any help would be greatly appreciated!
Thanks,
Kristy

 

[Dave Peterson]

I don't think I've ever used .protectsharing, but I have used this line instead:

' objXLBook.ProtectSharing Filename:=strFilePathName, _
SharingPassword:="Password"


objXLBook.SaveAs Filename:=strFilePathName, accessmode:=xlShared

 

[ctmom]

Thank you for responding! When I use SaveAs with a password, the
workbook will not open w/o a password (which I don't need) and the
Sharing can be unchecked (Tools, Share Workbook) so I can lose the
Change History if the workbook is unshared.

That is why I am trying to use protectsharing. Protectsharing does
work if I only protect the sheet and not the workbook also.
objXLBook.ProtectSharing
strFilePathName, , , , , "Password"

My purpose is to generate workbooks for the field to edit. I am locking
down everything I can! When they are returned I will use Change
History to view the changes in a consolidated format to ease data
entry.

Thanks,
Kristy

 

[Dave Peterson]

Everything I tried failed.

If I were a betting person, I'd be you can't do what you want.

 

[ctmom]

Hi,
Since I can do the same steps directly in Excel
but not in VBA code, is this a Microsoft bug?
Thanks,
Kristy

 

[Dave Peterson]

If you want to call it a bug, it's ok with me.

But maybe someone will post how it can be done--so maybe not???