Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Protected Worksheets (Excel 2003)

    I have an Excel 2000 file that has password protection on every worksheet. When I open the file in 2003, it brings up the Unprotect dialog box for every worksheet, asking for the password on each, or you can Cancel out of the boxes. Is there a way to not have it prompt for the unprotection on open? We have a lot of people we are switching to 2003 that will have this problem.

    I haven't had any luck finding an answer looking through this forum, the help menu or a book I have. I appreciate your group always coming through!

    Thanks,
    Patty

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protected Worksheets (Excel 2003)

    Do you have any code in the workbook open, that is trying to unprotect sheets?

    Steve

  3. #3
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Worksheets (Excel 2003)

    I have the following on open for each worksheet:
    ws.Protect userinterfaceonly:=True

    I assume this means they have changed how this works on opening in 2003...
    Patty

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Protected Worksheets (Excel 2003)

    Worksheets don't have an Open event, only workbooks do, so I don't understand what you mean by "I have the following on open for each worksheet".

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protected Worksheets (Excel 2003)

    What Steve asked is if you have any macro attached to the Workbook_Open event in the file causing the problem.
    Open the file and cancel all the prompts.
    Then press ALT + F11. This opens VBA editor.
    Once in there press CTRL + R to open the Project explorer window.
    Then double click on ThisWorkbook. (A module will open up on the right side. Check to see if there is any code attached to an event called Private Sub Workbook_Open)
    If there is code, let us know what it is and we can attempt help you from there!
    Regards,
    Rudi

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Worksheets (Excel 2003)

    Indeed it seems security has changed, this happens for me too.
    You'll have to add the password to the code I'm afraid.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Worksheets (Excel 2003)

    Sorry I wasn't more clear. That is the code I have in the "Private Sub Workbook_Open" in the "This Workbook" VBA. I have the following statement for each worksheet.

    worksheetname.Protect userinterfaceonly:=True

    If I have to add the password, like Jan mentions in her reply, then I'm going to need a way to secure the code so not everyone can look at it and see the passwords.

    Thanks,
    Patty

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Protected Worksheets (Excel 2003)

    You can set a password on the VB project:

    - Activate the Visual Basic Editor.
    - Select Tools | VBAProject Properties...
    - Activate the Protection tab.
    - Tick the "Lock project for viewing" check box.
    - Enter a password in the Password box.
    - Enter the same password in the box below that.
    - Click OK.

    Next time you open the workbook, you'll have to provide the password before you can view the code. Keep the password in a safe place - if you lose it, you won't be able to edit the code any more!

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protected Worksheets (Excel 2003)

    Hi,
    I'm hoping Hans's post answers your question on securing the code!

    PS: I don't think Jan has climbed over THAT fence, unless I missed a juicy post??!!
    Regards,
    Rudi

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Worksheets (Excel 2003)

    I'm sorry to inform you, that the VBA password can easily be cracked.
    All it requires is the right link to download a free utility to do so.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Worksheets (Excel 2003)

    Thanks for everyone's help! I have it working now.

    Patty

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protected Worksheets (Excel 2003)

    Even if you use 10 chars, with mixes of numbers and letters and symbols in te password. That may take days to crack!!!!
    Regards,
    Rudi

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Worksheets (Excel 2003)

    No. ANY VBA password can be cracked in split seconds. Makes no difference how complicated you make it, because the process of cracking has nothing to do with the actual cracking of the password. It just removes the protection entirely. It is relatively easy to do manually as well. All it takes is a hex editor and the knowledge of which character sequence to replace with which other sequence (and these two are independent of the PW).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protected Worksheets (Excel 2003)

    Well, don't let this piece of info spread! <img src=/S/noevil.gif border=0 alt=noevil width=25 height=17>
    Regards,
    Rudi

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Worksheets (Excel 2003)

    <img src=/S/hushmouth.gif border=0 alt=hushmouth width=16 height=16>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •