Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passwords, Public Constant

    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,

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Passwords, Public Constant

    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) : <pre>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 </pre>

    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
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passwords, Public Constant

    >hidden values

    is this the same as the "hidden name space"?


    Brooke

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Passwords, Public Constant

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

    Andrew

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passwords, Public Constant

    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!

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passwords, Public Constant

    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,

  7. #7
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passwords, Public Constant

    Have a look <A target="_blank" HREF=http://www.cpearson.com/excel/hidden.htm>here</A>. 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

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Passwords, Public Constant

    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

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passwords, Public Constant

    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

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passwords, Public Constant

    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?

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Passwords, Public Constant

    Something like this in code or the immediate window

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

    Steve

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Passwords, Public Constant

    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.<pre> ActiveWorkbook.Names.Add Name:="Password", _
    RefersToR1C1:="MySecretWord", _
    Visible:=False</pre>

    Hope this helps and sorry for the delay in replying.

    Andrew C
    Attached Images Attached Images

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Passwords, Public Constant

    Michael,

    See reply to john just above.

    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •