Aug 11 2020 07:53 AM
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.
Aug 11 2020 08:09 AM
SolutionAug 11 2020 08:14 AM
Aug 11 2020 08:29 AM
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.
Aug 11 2020 10:33 AM
Aug 11 2020 12:31 PM
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
Aug 11 2020 04:44 PM
Aug 11 2020 05:23 PM
Aug 11 2020 08:09 AM
Solution