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

Discussion in 'Microsoft Excel Programming' started by ctmom@comcast.net, Aug 3, 2005.

  1. Guest

    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
     
    , Aug 3, 2005
    #1
    1. Advertisements

  2. Re: 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
     
    Dave Peterson, Aug 3, 2005
    #2
    1. Advertisements

  3. Guest

    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
     
    , Aug 4, 2005
    #3
  4. Re: 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
     
    Dave Peterson, Aug 4, 2005
    #4
  5. Guest

    Hi,
    Since I can do the same steps directly in Excel
    but not in VBA code, is this a Microsoft bug?
    Thanks,
    Kristy
     
    , Aug 9, 2005
    #5
  6. Re: 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
     
    Dave Peterson, Aug 9, 2005
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jasons

    Share a workbook and protect

    Jasons, Nov 5, 2004, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    1,132
    Dave Peterson
    Nov 5, 2004
  2. WimR
    Replies:
    9
    Views:
    782
  3. Guest

    How can I share and un-share a workbook using a macro?

    Guest, Oct 4, 2006, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    3,507
    Guest
    Oct 4, 2006
  4. Corey
    Replies:
    4
    Views:
    640
    John Smith
    Nov 25, 2006
  5. Heera Chavan

    Protect Workbook and Share it.

    Heera Chavan, Nov 9, 2009, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    188
    Heera Chavan
    Nov 9, 2009
Loading...

Share This Page