Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing Password (Excel 2000 Pro)

    I have my Workbook set up to be read-only if you do not know the password. I wanted to change the password, so I went into the VBA code and deleted the old pass word, put in the new one, saved, exited and when I re opened the file, the new password was not valid, however the old one was. I looked in all the VBA code lines on all worksheets and there was not a trace (that I could see) of the old password. Here is a copy of the VBA String that is currently being used.

    Private Sub Worksheet_Activate()
    ActiveSheet.EnableAutoFilter = True
    ActiveSheet.Protect password:=evaderone1, contents:=True, userInterfaceOnly:=True
    End Sub

    I deleted "evaderone1" and replaced it with "password" on all sheets and the workbook file itself as well.

    If some one could tell me what I am doing wrong I would really appreciate it!

    Thanks!!

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Password (Excel 2000 Pro)

    You also have to change the password at the Workbook interface - as well as the code itself (which is only a supporting feature). HTH
    Gre

  3. #3
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Password (Excel 2000 Pro)

    I changed the VBA code in the work book file, but how do I change it at the work book interface??

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Password (Excel 2000 Pro)

    Try Tools|Protection|Protect Workbook. HTH
    Gre

  5. #5
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Password (Excel 2000 Pro)

    no, that did not work either. When you open the file, before you can even see what is inside on any of the sheets, it asks for a password. I did as you suggested and tried to use the protect workbook, but I still needed to use the old password to gain access to the sheets. :'( What now?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing Password (Excel 2000 Pro)

    All the worksheets are still protected with the old password. Unprotect all worksheets using the old password; you can do this manually or use a macro. From then on, the code should work OK and use the new password.

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Password (Excel 2000 Pro)

    Sorry - not my day. I didn't read your first post correctly. It seems that you are only protecting the individual Worksheets - rather than the entire Workbook. The Workbook (as a whole) is not meant to be protected. Either unprotect the worksheets using:<pre>Sub RemovePassword()
    Dim i As Long
    Dim strPassword As String
    strPassword = "OldPassword" 'change this as required
    For i = 1 To Sheets.Count
    Worksheets(i).Unprotect Password:=strPassword
    Next i
    End Sub</pre>

    or by using Tools|Protection|Unprotect Worksheet - for as many Worksheets as are required. Then save the workbook - with the amended code. When you reopen the workbook, the new code should produce the desired results.

    HTH
    Gre

  8. #8
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Password (Excel 2000 Pro)

    Here is the file. Password is evaderone1 Please help me to change this. One you have figured out how to do it, please tell me so that I can write it down. Thanks!!!!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing Password (Excel 2000 Pro)

    The password is a string; string values must be enclosed in double quotes. The current code uses

    ActiveSheet.Protect Password:=evaderone1, Contents:=True, UserInterfaceOnly:=True

    Since there are no quotes around evaderone1, Visual Basic thinks that it is the name of a variable, and since this variable hasn't been defined, it is equivalent to an empty string. In other words, you are protecting the worksheets without setting a password at all!

    Do the following:
    - Open the workbook and disable macros (you don't have macro security set to Low, I hope)
    - Unprotect all worksheets.
    - Change the above line in the code behind each sheet to

    ActiveSheet.Protect Password:="topsecret", Contents:=True, UserInterfaceOnly:=True

    (replace "topsecret" with whatever you want the new password to be, enclosed in quotes)
    - You can remove the code from ThisWorkbook - it is not applicable there.
    - Close and save the workbook.
    - Reopen the workbook and enable macros. The protection should now work as intended.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Password (Excel 2000 Pro)

    Is the attached what you wanted?

    I have to make several comments here:

    1- The workbook had some kind of corruption in it which I have been able to remove.

    2- The worksheet activate routines in this workbook was trying to set protection on the associated worksheet when it was activated. However, the protect method contained the parameter password:=evaderone1. The argument to the password parameter must be a string. Since evaderone1 was not included in double quotes, it was taken to be a variable. Since the variable evaderone1 was never defined or set to any value, it was inerpreted as a variant variable cotaining a null string. This resulted in the sheets being protected with no password.

    3- This is a perfect example of why every VBA module should contain the statement:

    <pre>Option Explicit
    </pre>


    Using this statement will require that all variables used in the module be declared in a DIM statement. If your modules had contained the above Option statement, then when you tried to run any code in the module, the VBE Editor would have complained that evaderone1 had not been defined and you would have know that there was a problem. If you open Excel and go to the VBE and select Options from the Tools menu and then click on the Editor tab, you can set the Require Variable Declaration option on. This will automatically insert the Option Explicit statement in all new modules that you create. You will have to manually insert it in any existing modules.
    Legare Coleman

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Password (Excel 2000 Pro)

    I've had a look at the workbook.<UL><LI>As <!profile=HansV>HansV<!/profile> says, the password should be inside quotation marks<LI>The same code was both in the ThisWorkbook object and in each sheet except the last - "12 Mos EAOS".<LI>There was no AutoFilter set up on the "Critical" sheet.<LI>Unless you state which range you are applying the AutoFilter to - such as <code> Sheet1.Range("A1").EntireRow.AutoFilter</code>
    - it will not achieve anything.[/list]Please find an amended version of the workbook attached - with the following suggestions implemented. <UL><LI>The password is now in quotes.<LI>For maintenance reasons, the code is now in the Workbook_SheetActivate event of ThisWorkbook. That way, if you add any new Worksheets, they are automatically protected. This means that "12 Mos EAOS" is now protected. There is no need to put code into individual sheets unless you want one or nore sheets to be treated differently from all the others.<LI>An AutoFilter has been added to "Critical".<LI>The code for AutoFilter has not been changed - although it does not at present accomplish anything. It was not completely clear whether it is meant to filter any Columns with Comment Boxes or not.[/list]HTH
    Gre

  12. #12
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Password (Excel 2000 Pro)

    Sorry for the confusion. The workbook itself that I have is set up and formated just the way that I need it to be. The copy that I sent you had all of the Information deleted due to the fact that it contains social security numbers and also to be able to reduce the size of the file.

    I have tried to follow your suggestions, however, I can not seem to be able to change the password by simply changing the word in the Double Quotes. Everytime I change what is in there, I save, close and when I open it back up, I still have to use the old password, but when I open the VBA code the new password is there in quotes. AUGGHHH!!! How frustraiting!!!

    As I'm sure you can tell, I am very much the Novice when it comes to VBA. All that I know, I learned in the classes taught by "Teachesms" that I took a while ago.

    Again, I am trying to change the password and need to know the steps.

    Thank you for the help!

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing Password (Excel 2000 Pro)

    Try the steps in my previous reply (<post#=465994>post 465994</post#>). I would also heed Legare Coleman's recommendation to set Require Variable Declaration and to include Option Explicit in all modules.

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Password (Excel 2000 Pro)

    The password you are talking about is NOT a sheet password, it is a workbook password. To change this password, you must do a File/SaveAs and click on the Tools button in the tool bar. Then click on General Options in the drop down menu. The password you are talking about is the second one in the dialog box labeled "Password To Modify." Did you try the workbook I uploaded for you? I changed it there.
    Legare Coleman

  15. #15
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Password (Excel 2000 Pro)

    Legare,

    You are awsome!!! I kept thinking about it over the weekend and realized that the password was set somewhere else, but I thought that it was some how changed in the properties section.

    Thank you very much for saving me!!!!!!

Posting Permissions

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