How to check password of protected sheet

[welchs101]

hi,

I have a macro where i protect one of the sheets in the file. I have the password hardcoded into the vba code. However, the user could change the password manually. So want i want to do is at the beginning of the macro check to make sure that the password in the vba code is the password used to protect the sheet. Any good ideas on how to do this?

[HaHoBe]

Hi, welchs101,

avoid giving the password to the users.

Sub welchs101()
Dim strPW As String
On Error Resume Next
ActiveSheet.Unprotect "mypassword"
If Err <> 0 Then
  While Len(strPW) = 0 Or strPW = ""
    strPW = InputBox("Enter new password:")
  Wend
End If
ActiveSheet.Unprotect strPW
End Sub

No guarantee that the password delivered will fit.

Ciao,
Holger

[Richard Buttrey]

Hi,

If the sheet is password protected then how could a user change the password unless they know what it is?

If you feel they may go behind the scenes to look in the VBE for the pw then protect the VBE with a password so that they have no way of seeing inside the macros and discovering it.

The point about passwords is that they're not meant to be easily discoverable and if there was some macro code that would get the password (in order to check it against a known value), then that would rather defeat the object.

[welchs101]

thanks. you are both right. but circumstances are such that i have to give the password to certain individuals who are not computer savy. currently they know how to unprotect a sheet manually but i am trying to do this via vba but i have to check for the possibility of someone changing the password just in case.

[Richard Buttrey]

Hi,

Given that the sheet is protected, what sort of access do you need to give to users? i.e. what do you want to allow them to change. Don't forget that you can unlock specific cells that are eligible to be changed when protection is switched on.

In addition note that when protecting the sheet there are various options you can tick(like deleting rows, sorting, filtering) so that these actions can still take place despite the protection.

[AkaTrouble]

you could create a user password that runs the macro

the macro could UN-protect the sheet using a password that looks like an instruction to the novice user reading the code

the macro would reset protected worksheet status

so the sheet would remain protected and if user changes their password it only enables them to run macro not access protected sheet

however refer to previous comments point of a password is to protect then only those allowed access should have password

other way is to hold the protected sheet in separate protected workbook that user can only call to not open

[mikerickson]

If only give selected people the password and you are concerned that those people will un/re protect the sheet with a different password, that sounds like 1) they need to be trained to never do that or 2) there is an HR problem with malicious employees, a problem that VBA cannot solve.

[welchs101]

thanks for the suggestions.

[LJMetzger]

First of all, Microsoft password protection is very weak, and a savvy user can 'beat the system'.

To defeat the efforts of unsavvy users, you could give the user an 'Encrypted' copy of the password. Users would have to use a command button or some other way to access a Macro which would have the User enter the encrypted password. The Macro would then decrypt the password, it and give the user access to the sheet.

This method only works if users don't have direct access to the Worksheet while it is unprotected. For example, a user would select a cell and then click a CommandButton that runs a Macro. The Macro would prompt the user for the data, and the Macro would unprotect/protect the worksheet as required. In order to run the Macro the first time, the User would have to enter the Encrypted Password.

See the attached sample workbook, which is based on a printable Character XOR (Exlusive Or) algorithm courtesy of Sebastion L. (circa 2003) at: http://www.codetoad.com/visual_basic_better_xor.asp
It seems superior to other XOR algorithms I have seen, as the other algorithms usually have NON-Printable characters in the 'Encrypted Password'.

Lewis