Password Potect through VBA from Cell

[JBruner79]

So I am trying to password protect my workbook so that it won't open unless you have the password. But here's what i need:

I need to have a cell that contains the password so that it can be changed whenever and easily for someone who has no excel or computer literacy, without going through settings.
I need code to only open workbook if password entered matches what is in the cell.

Is there a way to do this? I have looked and can't figure it out. So any help would be greatly appreciated.
Thank you!!!

 

[Fluff]

If the password is in a cell that is easily accessible, what's the point, as anyone can what the password is?

 

[JBruner79]

If the password is in a cell that is easily accessible, what's the point, as anyone can what the password is?

There are 5 people who need access, so the password it to keep everyone else out. It doesn't matter if the other 5 know the pw.

 

[Fluff]

But if the password is in a cell what's to stop everyone else looking at it?

 

[JBruner79]

Because it wouldn't be used by everyone in the office, just the managers. I honestly didn't think when I was asking for help that I would receive such interrogation. Its a need I have, and can't figure it out myself. It really doesn't matter if you agree with the why its needs to be this way, or if you understand why. I am here like everyone else, asking for guidance so that I can learn and improve my skillset, at the same time provide my office with the resources they need.

 

[Danmc]

Private Sub CommandButton1_Click()
Dim wb As Workbook
Dim psswd As String
psswd = InputBox("enter password")
Set wb = Workbooks.Open(Filename:="password.xlsx", Password:=psswd)
End Sub

and here is the save macro:

Sub PasswordSave()
Application.DisplayAlerts = False
Application.ScreenUpdating = False


ActiveWorkbook.SaveAs Filename:="c:\Put_Path_Here\password.xlsx", FileFormat:= _
xlNormal, Password:=Sheets("password").Range("A1").Value, WriteResPassword:=Sheets("password").Range("A1").Value, ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close
End Sub

the problem in doing so is that if the user types a new password in A1, saves it and forget's it, then the file cannot be recovered (except using third party tools)

 

[Joe4]

Because it wouldn't be used by everyone in the office, just the managers. I honestly didn't think when I was asking for help that I would receive such interrogation.

He is simply trying to make you aware that the method that you propose won't be sure at all, which kind of defeats the purpose of password protecting (what is the sense in password protecting if the password is readily visible to all?).

Sometimes, the question isn't quite clear, so we need more details to fully understand it. Remember, we are not familiar with your problem, all that we have to go on is what you provide here. So, if the question is lacking in detail, we often need more information to fully understand the question and provide a solution. It is not an "interrogation", it is simply trying to gather the necessary information to provide you with a solution (don't bite the hand that is trying to feed you!).

Many times people ask questions and they get stuck on an idea that won't really accomplish what they are trying to do. If we have a clear understanding of what the ultimate goal is, we can often offer alternative solutions that will accomplish that goal, instead of providing solutions based on faulty logic.

 

[Fluff]

Put this in the ThisWorkbook module

Private Sub Workbook_Open()
   Dim Pwrd As String
   
   Pwrd = InputBox("Please enter a password")
   If Pwrd <> Sheets("sheet1").Range("A1").Value Then
      ThisWorkbook.Close False
   End If
   
End Sub

But if someone changes their macro settings, or opens the file in safe mode, it won't work.