Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can protect worksheet then workbook but not Protect and Share in code
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can protect worksheet then workbook but not Protect and Share incode
Everything I tried failed.
If I were a betting person, I'd be you can't do what you want. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can protect worksheet then workbook but not Protect and Share in code
Hi,
Since I can do the same steps directly in Excel but not in VBA code, is this a Microsoft bug? Thanks, Kristy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can protect worksheet then workbook but not Protect and Share incode
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??? 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can protect worksheet then workbook but not Protect and Share in code
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can protect worksheet then workbook but not Protect and Share in code
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Share Workbook/Protect Sheet Question | Excel Discussion (Misc queries) | |||
Custom views, filters sensitive, in a share workbook an protect sh | New Users to Excel | |||
How to protect the share workbooK | Excel Worksheet Functions | |||
share a workbook but protect the formulas | Excel Discussion (Misc queries) | |||
Share a workbook and protect | Excel Programming |