Protect / Unprotect Macro

[jason.hampton]

Greetings! This is my first post.

I am using Office 2007 Prof. I found a web site for a "Proect / Unprotect" macro instructions. http://www.ozgrid.com/VBA/excel-work...rotect-all.htm

My question is, can the code in the above proceedure be modified so it asks you to enter your password twice to check for accuracy?

~*~*~*~*~*~*~*~*~*~

Please Login or Register  to view this content.

~*~*~*~*~*~*~*~*

Best Regards,
Jason Hampton

[Mallycat]

Yes it can be modified. How about you follow the instructions on the site and produce a spreadsheet that works. Then post the sheet here and I am sure someone will modify it for you.

[jason.hampton]

Yes it can be modified. How about you follow the instructions on the site and produce a spreadsheet that works. Then post the sheet here and I am sure someone will modify it for you.

Mallycat,

Stupid question, but how do you post the sheet? This is my first post on this forum.

[john55]

try this
Forum rules...()Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[jason.hampton]

Sorry for the confusion. During the first post, I could not find the paperclip.

Attached is a .xlsm file. I would like to see if the code can be modified to re-enter your password for verification while "protecting" the workbook.

Here is a web link for reference: http://www.ozgrid.com/VBA/excel-work...rotect-all.htm

[Mallycat]

Good job on getting this far. Here is the modified code. We try to keep these forums organised and easy to read, so can you please go back to your first post and wrap your VBA code in the code tags. Click "go advanced", highlight the code, and then click the # button.

Now that you have this, see if you can work out how to change the Title of the userform so that it reads "Enter Password to Protect" or "Enter Password to Unprotect" or something similar.

EDIT: You could also change the the button label to toggle between "sheet is protected, click to unprotect" and "Click to protect sheet"

[jason.hampton]

Mallycat,

Thanks for your assistance! This works great. I figured out how to change the title. But don't know how to make it toggle between protect and unprotect windows. I don't have a tremendous amount of VBS experience. Any asssistance will be appreciated.

[Mallycat]

the second label and text box are invisible by default. This means the default is for when the sheet is protected, therefore change the caption to read Unprotect Worksheet.

The Code checks to see if the worksheet is currently unprotected, and if it is, it makes the second label and second text box visible. So you need to add code to also change the caption in the case that the worksheet is unprotected.

Please Login or Register  to view this content.

[jason.hampton]

Mallycat,

I can't seem to get the code to work correctly. What am I doing wrong?

Please Login or Register  to view this content.

[Mallycat]

Can you post the whole sheet with the code back here again

[jason.hampton]

Mallycat,

See attached. Thanks for your assistance!

[Mallycat]

The problem is that you have put the Sub Showpass() on the wrong module. You have it on the userform1 module, but it should be on the Module1 module.

Copy the Sub Showpass() from the userform
then delete showpass() from the userform
delete the current showpass() from the module
paste the showpass() you copied from the userform on the module1.

Then it will work

[john55]

...and if you want to have yr password "masked" you can use

Please Login or Register  to view this content.

[Mallycat]

Here is the fixed file, plus a video (in the zip file) that shows what was wrong and how to fix it.

for some reason I can't add a zip file with the video - I get an error. I have loaded the video here
https://vimeo.com/41459647

It should be visible within 30 mins of this post

[jason.hampton]

Here is the fixed file, plus a video (in the zip file) that shows what was wrong and how to fix it.

for some reason I can't add a zip file with the video - I get an error. I have loaded the video here
https://vimeo.com/41459647

It should be visible within 30 mins of this post

Mallycat,

Thanks! This works great. I will watch the video in a bit. I need to update these macros in several large workbooks. Thanks again for your help.

[Mallycat]

This version protects every sheet except the first sheet. It relies on the first sheet being called "Sheet1". If you change the name, you need to change the code