PC Review


Reply
Thread Tools Rate Thread

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

 
 
ctmom@comcast.net
Guest
Posts: n/a
 
      3rd Aug 2005
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

 
Reply With Quote
 
 
 
 
Dave Peterson
Guest
Posts: n/a
 
      3rd Aug 2005
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

(E-Mail Removed) 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
 
Reply With Quote
 
 
 
 
ctmom@comcast.net
Guest
Posts: n/a
 
      4th Aug 2005
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

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      4th Aug 2005
Everything I tried failed.

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



(E-Mail Removed) 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
 
Reply With Quote
 
ctmom@comcast.net
Guest
Posts: n/a
 
      9th Aug 2005
Hi,
Since I can do the same steps directly in Excel
but not in VBA code, is this a Microsoft bug?
Thanks,
Kristy

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      9th Aug 2005
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???

(E-Mail Removed) 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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... Corey Microsoft Excel Programming 4 25th Nov 2006 05:57 AM
Can't share workbook. The "Share Workbook" option is off. Why? =?Utf-8?B?RG95bGUgRGllbmVy?= Microsoft Excel Misc 4 24th Apr 2006 06:56 PM
Passwords Disappear in OpenOffice (Protect Worksheet, and Protect Workbook) msnews.microsoft.com Microsoft Excel Discussion 6 27th Dec 2005 09:14 AM
if greater then 99 then 1 if greater then 199 then two =?Utf-8?B?Ti5SLg==?= Microsoft Excel Worksheet Functions 2 23rd Jun 2005 06:14 PM
Need help with a log off/log on problem...can log off then log on dif. user and system will be slow loading user settings, then play start up music, then show wallpaper, then freeze. Ralph Malph Windows XP General 2 9th Feb 2005 07:05 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:14 PM.