Capture workbook password

[VegasNath]

Is there a formula that can be used to capture the active workbook's password?

[HansV]

Of course not! That would make the password useless if someone else looked over your shoulder!

[VegasNath]

My objective is as follows:
I use a control workbook with macro's that manipulates other files. Those files are password protected with the same password as the control workbook. My idea is that if i can get to the active workbook's password, then I have no need to document the password in the code. Not foolproof i know, just an added layer of protection from the standard user, and maybe a little more 'audit compliant'.

Obviously you have to know the password to enter into control workbook, however I'd rather not have any need to document it. Hope this makes sense.

[HansV]

The idea of a password is that you do *not* document it in the file protected by that password! You can document it in another (password-protected) file, or in a password application (or, like most users, on a post-it note stuck to the underside of the keyboard )

The Workbook object in Excel VBA has a property Password, but it always returns ********, whether a password has actually been set or not.

[StuartR]

...Obviously you have to know the password to enter into control workbook, however I'd rather not have any need to document it...

Could you provide a wrapper that prompts the user for the password and opens the control workbook. You could then store the password in volatile memory.

[VegasNath]

...Obviously you have to know the password to enter into control workbook, however I'd rather not have any need to document it...

Could you provide a wrapper that prompts the user for the password and opens the control workbook. You could then store the password in volatile memory.

Hi Stuart,
Would you mind elaborating a little? Your suggestion sounds like what I am looking for, but I am unsure how to create that set-up.
Thanks.

[StuartR]

...
Hi Stuart,
Would you mind elaborating a little?
...

My thought was that you could create (for example) a simple VBA programme that prompts the user for a password. This programme could assign the password to a variable and then open the protected workbook, using code like...

Code: Select all

Public Sub OpenWithPassword()

Dim wb As Excel.Workbook
Dim strPwd As String

strPwd = InputBox(Prompt:="What is the password?")
Set wb = Excel.Workbooks.Open(Filename:="C:\Whatever.xlsx", Password:=strPwd)

End Sub

[HansV]

I see that Stuart already replied, but for what it's worth:

Let's say that the password-protected workbook is Workbook A.
Create a second workbook Workbook B with a macro that prompts the user for a password and trie to open Workbook A:

Code: Select all

Dim strPw As String

Sub OpenA()
  strPw = InputBox("Enter password")
  On Error GoTo ErrHandler
  Workbooks.Open FileName:="Workbook A", Password:=strPw
  Exit Sub

ErrHandler:
  MsgBox Err.Description, vbExlamation
End Sub

The password will be readable in the inputbox. For a more sophisticated solution, use a userform with a text box for the password; set its PasswordChar property to * to hide the passweord.

[VegasNath]

Thanks Stuart. I have a thought that may work...

Instead of protecting the control workbook, I could use it's open event to capture the password to memory, and force a close event if the password is unknown. That way, if you cannot open the control workbook, you cannot use the macro's within to manipulate the other wb's. This also means that the password in memory could not possibly be incorrect, and therefore the macro's that will use it could not possibly fall over (which is my primary concern in this whole exercise)

Any thought's on this approach? Keep in mind that I am thinking from an audit compliance point of view. All of the wb's concerned are on a secure drive with limited access, so I don't have protection concerns, per-se.

[HansV]

Couldn't you limit access to the folder to those who need it?

[VegasNath]

Couldn't you limit access to the folder to those who need it?

Hans, that is the whole point, the folder is limited to only those that need it (about 6-8 people). IMO, the passwords are overkill, but i don't make the rules. My objective is to ensure that the macro's in the control wb can execute without error, hence favouring a 'capture' of the "certainly correct" password used to gain entry to the control workbook, over a user input approach, which leaves room for error.

[HansV]

Windows-level security is better than workbook-level security. If someone is clever enough to break your Windows-level security, they will almost certainly be able to break the workbook password. But if you do want to use a workbook password, storing it in the workbook itself in a readable way defeats the purpose...