How to set unprotect password to equal a cell value

**Chaylon** · 03-12-2010, 11:24 PM

Hello,

I would like to set the sheet unprotect VBA formula to use a cell value as the password to unprotect...purpose of this is I want to be able to edit that cell at anytime to update all instances of the "unprotect" password that are used over dozens of macros....

I was trying,

----------

Code:

Sub click()
Dim SH As Worksheet
Const PWORD As String = ("Sheet1!b1")
Worksheets("Sheet1").Protect Password = PWORD

End Sub

--------

But when it locks, I can not unlock it using the value in B1....

Any ideas?

**JBeaucaire** · 03-13-2010, 03:09 AM

Hi Chaylon... Welcome to the forum. Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to EDIT that post above and put code tags around that code you used. (Like shown in my signature)

=========
Perhaps simpler would be to select the cell B1 in sheet1 and give it a NAME of PWord in the Name box. This "helps" disguise it's location, too.

Then in your macro, simply refer to the named range:

Code:

Worksheets("Sheet3").Protect Range("PWord")

**Chaylon** · 03-13-2010, 04:27 PM

Thanks a LOT! now I just have to go through all 80 some macros and update the VBA, lol

**JBeaucaire** · 03-14-2010, 05:52 AM

Um, 80 macros?

You should consider putting the variable into a Constant or a Global variable. Then update the macros to utilize the ONE variable you've declared for that purpose. Hopefully that results in less work to maintain the macros...

Or perhaps you can use the CTRL-H (Replace) function to update all the macros at once...maybe.