Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Can protect worksheet then workbook but not Protect and Share incode

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

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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default Can protect worksheet then workbook but not Protect and Share in code

http://tutorialway.com/protection-in...eets-workbook/
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default Can protect worksheet then workbook but not Protect and Share in code

http://tutorialway.com/protection-in...eets-workbook/
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Share Workbook/Protect Sheet Question Johnny Excel Excel Discussion (Misc queries) 2 October 4th 07 07:14 PM
Custom views, filters sensitive, in a share workbook an protect sh Andre Fernandes New Users to Excel 0 October 31st 05 10:36 PM
How to protect the share workbooK Calculate Date range Excel Worksheet Functions 0 September 19th 05 12:02 PM
share a workbook but protect the formulas clemrogan Excel Discussion (Misc queries) 2 August 9th 05 09:25 PM
Share a workbook and protect Jasons Excel Programming 1 November 6th 04 12:26 AM


All times are GMT +1. The time now is 11:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"