Passwords, Public Constant

Topic

New Reply

Hi all,

I have a workbook with 10 sheets (6 visible, 4 hidden). Currently, I am setting the workbook password and the sheet protection password with the following code:

Public Const PW As String = “abcdefg”

While this is efficient while there are few users, I worry that over time someone will figure out the code or use software to crack the code. I would like the user to be able to change the current password, enter a new password, and confirm the new password (much like the Excel 2000 password dialog).

I seem to be getting hung up with the logic of storing and switching the new password with the old password within the variables in VBA. I am thinking my code will have to do the following:

1.Disable screenupdating and set enablecancelkey=xldisabled
2.Unprotect the workbook with the old password
3.Unprotect each sheet in the workbook with the old password
4.Protect each sheet in the workbook with the new password
5.Protect the workbook with the new password

With all the wisdom exhibited in this forum, surely someone here has done this before. Could anyone shed some light or code on this?

Thanks,

Reply | Quote

Replies

Mike,

Instead of using a VBA constant, why not use a worksheet constant, which can be set up using Insert, Name and Define, but instead of using a range, enter a value and you have a worksheet constant which is accessible to any worksheet or any VBA module. You can set the visible property to false so that the name does not show in the names dialog, and unless somebody knows the name of the constant, they cannot access the value. The only advantage of using a constant in your case, is to verify the old password before allowing a change. Otherwise you have to resort to On Error traps, and frankly that may the most secure method, as then there is no copy of the password available. However, assuming you are happy enough with the approach you outlined, I would suggest some code like the following as a starting point (this uses a userform to get the old PW and two entries of the new) :

Sub ChangePassword()
    Dim OldPass As String, StoredPW As String
    Dim NewPass1 As String, NewPass2 As String
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    UserForm1.Show
    OldPass = UserForm1.TextBox1
    NewPass1 = UserForm1.TextBox2
    NewPass2 = UserForm1.TextBox3
    If NewPass1 = NewPass2 And NewPass1 > "" 
        ActiveWorkbook.Unprotect Password:=OldPass
        For Each ws In ActiveWorkbook.Worksheets
            ws.Unprotect Password:=OldPass
            ws.Protect Password:=NewPass1
        Next
        ActiveWorkbook.Protect Password:=NewPass1, Structure:=True, Windows:=False
        ProcessPass = False
    Else
        UserForm1.TextBox1 = ""
        UserForm1.TextBox2 = ""
        UserForm1.TextBox3 = ""
        MsgBox ("Password Not Changed")
    End If
    Unload UserForm1
    Application.ScreenUpdating = True
End Sub 

I am attaching a workbook with the above adapted to include the use of worksheet constant. If you decide it is useful you can enhance it further, as I did little in the way of error checking etc. The disadvantages of this method include the fact that the password could be vunerable to prying eyes, and if the password is changed outside of the procedure, problems could arise unless the constant is also updated. Advantages are that your workbook and w/sheet passwords can be changed in one operation. Also a workbook_close event could ensure all protection is in place when the book is closed. Also if you forget the password, you could retrieve it by accessing the value in the constant.

Apart from the worksheet constant, Excel has what are called hidden values, which are application wide and would be more difficult to access. Any code modules accessing the password value would have to be protected.

Not much wisdom, but maybe a starting point for you.

Andrew C

Reply | Quote

>hidden values

is this the same as the “hidden name space”?

Brooke

Reply | Quote

Yes, Brooke, Hidden Name Space is the correct terminology – it escaped me at time. Thanks

Andrew

Reply | Quote

Anyway you two could clue me in on using the “Hidden Name Space”? I searched both Excel and the Excel VBA help and came up with blanks. How do you access it and/or refer to it?

Thanks,

Reply | Quote

Have a look here. I only asked because I’ve never seen a real world use of this and was hoping Andrew would expand on his comments if this was what he was talking about… You have the helm, Mister Cronnolly.

Brooke

Reply | Quote

Mike, the link Brooke posted re Hidden Name Space should give a good idea of what they are and how they might be used. In this case what I had in mind for a possible use was to hold the password while the book is open, thus enabling the removeal of the sheet constant holding the actual password, only to be recreated when saving the workbook. It is just an extra security consideration assuming that less people know about the hidden space (or how to access it) than know about invisible constant names.

One of the best uses for the hidden space is, as it is Application wide, for sharing constants or variables between open workbooks, i.e. workbooks X and Y could have access to the same value.

Andrew

Reply | Quote

Andrew, I must disagree with you….you exhibit much wisdom by not only the code, but the logic behind the two different approaches. I must say, I was not aware that you could define a value instead of a range. Wow! In my workbooks and code that I have created, using this approach could be a much preferred solution to some of the tasks I have tackled.

Many thanks! That bit of info sorta gets me thinking on a 4th dimension!

Reply | Quote

Andrew,

I am having difficulty in recreating the value (Insert, Name, and Define) for the worksheet constant. Apparently I’m doing something wrong in Cell B1. When I run the code I receive the error message: “Application-defined or object-defined error”.

John

Reply | Quote

John,

Not sure what you are dong to generate that error, except that you should have no need to use cell B1 for this purpose. To create a Name that referes to a constant rather than a range (cell), you insert the value of the constant into the Refers To box. See attached screenshot. The example assigns the valu “MySecretWord” to the defined Name “Password”, so that entering =Password in a cell should display MySecretWord.

The following does the same thing via code, and also makes the Name invisible in the Go To dialog box.

    ActiveWorkbook.Names.Add Name:="Password", _
            RefersToR1C1:="MySecretWord", _
            Visible:=False

Hope this helps and sorry for the delay in replying.

Andrew C

Reply | Quote

Andrew

“You can set the visible property to false so that the name does not show in the names dialog”

How do you do this?

Reply | Quote

Something like this in code or the immediate window

ActiveWorkbook.Names.Add Name:=”myName2″, RefersToR1C1:= _
“=Sheet1!A1”, visible:=false

Steve

Reply | Quote

Michael,

See reply to john just above.

Andrew

Reply | Quote