1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003 Password Protect

Discussion in 'Business Applications' started by computerman29642, Mar 27, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Is there a way to have an Excel worksheet determine if the worksheet is password protected? if the worksheet is not password protected, then the worksheet gets protected when the user closes the Excel file and if the worksheet is already protect, then it does nothing.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    When you say worksheet, is it the only sheet in the workbook?

    If so, there's Save As > General Options > Password to Modify.
     
  3. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    No, It has about 4 worksheets within the workbook.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    OK.

    This is a reasonable subject, IMO; so I'll start with this:

    Answer = "Yes. You can use VBA to protect specific sheet(s) at Workbook_Open. But what if the user opts for Disable Macros?".
     
  5. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    That is not a problem. There will be other coding behind the worksheet(s), so the user will always select to enable macros.
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I don't understand "the user will always select to enable macros". How will you ensure that s/he doesn't choose to disable? (in which case the code to protect won't run)

    The bottom line is that Excel is not a secure application. Give me a password protected sheet & I'll crack it. ;)
     
  7. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    The user who will be using this workbook knows that there is coding behind the worksheet(s), and has several other Excel workbooks that have macros within them. The user knows that the macro will not run if they are disabled.

    I know that the password protect does not really provide any security, but it is just a defense mechanism to be sure that formulas do not get deleted, or other data does not accidently get deleted.

    Yeah, I have cracked a few macro passwords in my time...LOL
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "I know that the password protect does not really provide any security".

    As long as you know that Excel uses a very simple encryption system, then OK.

    Just place the code to protect the relevant sheet(s) in a Workbook_Open event in the workbook module.

    Access the wb module by right-clicking the Excel icon left of "File" in the menu bar and choosing "View Code" from the pop-up menu.
     
  9. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Now I actually want the code to run when the workbook is closed. I find many times that a user will unprotect the worksheet, forget to place the password protect back on the sheet, and something accidently gets deleted.

    Also, what if the worksheet is protected at the time the workbook is closed, will an error message appear because it is already protected, or will the workbook just close?
     
  10. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Bomb #21,

    I used the Workbook_BeforeClose(Cancel As Boolean) event, and it worked perfectly.

    Thank you for all your help and efforts.
     
  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    If the code runs when the wb opens, it's the same thing effectively. Possibly if it runs on close, it will count as a change, prompting the user to shout "Why are you asking me to save?; I already did!"

    Didn't when I tried it. Give it a go. :)
     
  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Each to his or her own. :D (y)
     
  13. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Bomb #21,

    Ok....I see what you are saying. If the user forgets to password protect the sheet when the workbook is closed, then the next time the workbook is opened the worksheet will once again be protected.

    I guess I have not had enough coffee yet this morning....my mind is running a little slow still.....LOL

    Thank you very much for the help. I am going to use the Workbook_Open event as you suggested.
     
  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Good call. After all, who knows what happens to Workbook_BeforeClose when Excel (the entire application) terminates unexpectedly. :eek:

    Have a good one. :)
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/697780

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice