Macro to Include Password

[Frank Drucker]

I want to create a macro that will protect and unprotect a sheet with a password.

I tried to "record" a macro that had a password but the resulting macro had none.

[#2 · Accepted solution]

Sheets("sheet1").Select ActiveSheet.Protect Password:="" Sheets("sheet1").Select ActiveSheet.Unprotect Password:="" Change Sheet1 to the name of your sheet. If you want an actual password, place it in the "".

[#3]

Use below code Sheet1.Unprotect "123" Sheet1.Range("A1").Value = "ABC" Sheet1.Protect "123" where 123 is password, so just replace it.

[#4]

@Frank Drucker 

Maybe this file helps you, I have prepared it so that you can see and play the VBA code.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

* Beware of scammers posting fake support numbers here.

[#5]

Thanks all for your help. 

I'll give your suggestions a shot.

@Frank Drucker 

[#6]

@NikolinoDE 

Hi

I am interested the piece of vb you created to run the macro. I have not done much VB and not for a long time so I have forgotten most of it. However, I was wondering whether you could help whether I understand this correctly:

1. you created  a sub called secure and attached it to the button. 

2. you created 3 variables with different characteristics (p1, p2, i)

3. p1 and p2 are collected through an input box

4. then you present the user with messages depending on whether the p1 or/and p2 are empty or not equal to one another

5.  variable 'i' holds the number of sheets and then you count the sheets and populate 'i' with that number

6. hen you tell excel to protect i number of sheets with password p1

7. finally you send message to user that sheets are protected.

So that all seems clear to me so please correct if not. 

In sub number 2 named unsecure () you have the same variables with the same variable names however since declared inside the sub/sub end does that mean that these are new variables? If you were to have wanted to use the same ones wouldn't you have needed to declare the variables outside the sub/end sub statements to make them available to both functions?

So the bit I don't understand, is that I don't see anywhere the entry of the second input box (unsecure) to be compared to the entry of the first input box (secure)?!?!? Obviously it works but I just don't understand why. 

I tried to change the names of the variables in the second function (sub unsecure()) and to see whether it still works and it does. Is the 'Sheets(i).Unprotect p3' function a set function in excel vb where unprotect simply compares value p3 to the value used to protect the worksheet and you don't need to tell excel explicitly to compare values?  Sorry, this is rather long....

5. if theses requirements are fulfilled you send message that sheet is protected

6

[#7]

Sorry, I didn't do this.

Wish I could help...

@Poogermum 

[#8]

Although I did not write it either, I think I can offer some additional explanation.

Yes, the variables declared in "unsecure" are separate from the variables with the same name in "secure". Because they are declared inside "unsecure", "secure" cannot "see" them. If the variable names were declared at the top of the module outside of the "subs," then all of the procedures within the module could see them (and, perhaps, all of the procedures in all modules, depending on whether the variables are declared "private" or "public").

You could save the password input in "secure" to a variable at the module level that both procedures can "see," but that variable will be destroyed when the workbook is closed/reopened unless it is saved somewhere.

If you want to know more, then you could search for "vba variable scope" and, I would think, should be able to find more information.

If a bad password is given to "unsecure," then vba will throw an error when it attempts to unprotect the sheets. You will note the "on error goto" statement right before it tries to unprotect the worksheets telling it to jump to the label "fehler:," which tests the error status (number) and generates a message if there was an error (error number is not zero).