VBA Cell Password

[sassriverrat]

Good Morning!

I'm trying to use a cell as the password for a workbook. Basically, I want the password easily changeable for a user- so a hidden sheet (Developer) houses a cell that has the password put in. If a user wants to change the vba password (that unlocks sheets and such), all the user must do is show the Developer sheet and then change the password in the cell. I know the .text extension isn't correct but I wasn't sure which ending - assuming this piece of code would work.

thanks

    Dim pass123 As Double
    pass123 = Sheets("Developer").Range("B16:E16").Text

 

[Jaafar Tribak]

Are you referring to the workbook protection (Structure and Windows) or to protected individual sheet(s) ?

 

[sassriverrat]

sheet protection- I use a msgbox for the password to show the hidden sheet (developer). Within the Developer sheet, I have a button that unlocks all sheets (so the password is in the code as "1234567890" and I would like it to be pass123 and then pass123 references Cell B16. B16 has the password. Basically this would allow a user to easily change the password without having to go into the vba code.

Thanks!

 

[sassriverrat]

Bump?

 

[Jaafar Tribak]

I am not sure I understand correctly but if you want the password to change according to a cell value then you could do something like this :

Code in the Worksheet Module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A1").Address Then
        Me.Protect Target.Value
    End If
End Sub

Where Cell A1 is the cell that stores the new sheet protection password ...Change store cell as required.

 

[sassriverrat]

that should do it perfectly!

Thanks!

 

[sassriverrat]

Hmm...actually I wonder if it needs to be a function?

Not quite what I wanted. I have a few buttons that unlock sheets- an example of one is below. I'd like to use a cell (say sheets("Developer").Range("A1") as the password so it can be easily changed by just changing whatever is in that cell. If the cell as 123, then the password is 123, etc.

Thanks!

Sub RemoveProtection()Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect "1234567890"
Next sh
End Sub

 

[sassriverrat]

@Jaafar Tribak I knew there was something I wasn't thinking about.

So no, what you had was the right idea but it can't be in the worksheet module. All but 3 of the worksheets are continuously being deleted and re-added so their module contents wouldn't stick around. I have, for example, a module that creates a sheet, formats it, adds all of the appropriate information, and at the very end protects the sheet. I'm looking for a way to change the piece of code (below) to make the password come from sheets("Developer").Range("A1") where the password could then be changed by the user.

Protects Sheet    ActiveSheet.Protect Password:="1234567890", UserinterFaceOnly:=True
    ActiveSheet.EnableSelection = xlUnlockedCells