Cannot change protection password

[holmes722]

Hi all-

I have a spreadsheet that was given to me, which I used as a template. It already had password protection in it for each worksheet, and the macros are also written to unprotect before performing the actions, the re-protect at the end. Everything works fine except I cannot change the password. The thing is is that the password in the macro code is not the same as the actual password which you have to type in when unprotecting the worksheets. For example the password in the code is "IPP" and the password you have to actually enter into the unprotect sheet box is "AAAABBBABBB&", yet all the macros still work like this. When I attempt to unprotect the sheet (with the AAAA... code) and re-write the password codes in the macros to say "###" then re-protect the sheet with the new password and run the macros it'll work fine, but when I save and close and then re-open the password required to unprotect the sheet reverts back to the "AAAABBB...." and then the macros stop working because the passwords don't match (even though they didn't match before!!!) There is no encryption password or workbook password as far as I can tell, only the worksheet protection. Why cannot I no get the password changed?

 

[mole999]

"AAAABBBABBB&" looks like what is generated by a password breaker ( a way to get ypou in to the sheet when you don't know the original). IPP hasn't been set up as a variable has it ?

 

[holmes722]

Mole: Sorry I am a noob so you may need to walk me through this. I do not see anything in the Dim sections that would make the password a variable. Here is some code for example. When I run the macro it debugs at the last line, this is after I have unprotected with old password and protected with new one. Also, I think you are right about the breaker, because the IPP password still works along with the AAAA... one.

Sub MakeNewDay()
    Dim TotalSheets As Integer
    Dim i As Integer, j As Integer
    Dim WorkBookName As String
    Dim NewReportName As String
    Dim Message As String, Title As String
    Dim Today  As Single
    Dim LastReportName As String
    Dim TodayText As String
    Dim isGridLines As Boolean
    Dim isDisplayHeadings As Boolean
    Dim NewSheetName As String
    Dim TtlSheets As String
    Dim ReportDates As New Collection
    Dim MySheet As Object
    Dim MyBook As Object
    Dim Item   As Variant
    '
    ' Count how many sheets are in the current workbook
    '
    TotalSheets = ActiveWorkbook.Sheets.Count
    TtlSheets = ActiveSheet.Name
    Sheets(TtlSheets).Activate
    ActiveSheet.Unprotect Password:="###"

[end code]

 

[mole999]

I would agree that the password is
Password:="###" as text

rather than
Password:= ### as variable
which is what i was thinking might be declared elsewhere

do you have anything in the workbook code i.e workbook open code, some where in there I would expect another password statement that would apply the password in the first instance

 

[holmes722]

The only thing I can see password wise is in the workbook properties table there is a series of * in the 'password' and 'write password' boxes. When I attempt to write into these boxes and hit enter it goes back to the same number of * and I cannot tell if anything is changed, and when I input the new password here it makes no difference. There is nothing in the workbook code itself identifying a password. Would there be something in this properties box that is preventing a password change?

Here is the workbook open code

Private Sub Workbook_Open()
    Dim Today  As Single
    Dim AbortProc As Boolean
    Today = Date
    UserForm1.AddButton.Visible = True
    AbortProc = ValidDateCheck(Today)
    If AbortProc Then
        UserForm1.AddButton.Visible = False
    End If
    UserForm1.Show vbModeless
End Sub

[end code]

And the sheet activate code:

[code]
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim Today  As Single
    Dim AbortProc As Boolean
    Today = Date
    UserForm1.AddButton.Visible = True
    AbortProc = ValidDateCheck(Today)
    If AbortProc Then
        UserForm1.AddButton.Visible = False
    End If
    UserForm1.Show vbModeless
End Sub

[end code]

 

[holmes722]

Mole: I think I figured it out. The box for 'EnableAutoRecover' was set to true, so I set it to false and then changed all the passwords in the code and also on the 'protect worksheet', then saved it, opened it back up and reset the autorecover back to true and it seems to be working now. Hopefully this was all it was, maybe a glitch but it seems to be working now. I'm sure this would need to be done again if the password was to be changed again.

Thanks for all your help and time!!

 

[mole999]

I'm not sure how to read the * in the properties, its not an area I have experimented with

if you do a global find over the vba any other PASSWORD entries

Youtube do carry a number of how tos, which might be informative, and there are many commercial products that may or may not be of value