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

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

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

[email protected] wrote:
>
> 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

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

Everything I tried failed.

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



[email protected] wrote:
>
> 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

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

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???

[email protected] wrote:
>
> 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