Solved: Excel 2003 Password Protect

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
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.
 
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?".
 

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.
 
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. ;)
 

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
 
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.
 

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?
 

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.
 
Joined
Jul 1, 2005
Messages
8,546
gamecockfan said:
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.
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!"

gcf said:
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?
Didn't when I tried it. Give it a go. :)
 
Joined
Jul 1, 2005
Messages
8,546
gamecockfan said:
Bomb #21,

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

Thank you for all your help and efforts.
Each to his or her own. :D (y)
 

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.
 
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. :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top