Vba code with unprotect that worke on Excel 2010 doesn't work on Excel 2016

%3CLINGO-SUB%20id%3D%22lingo-sub-1357980%22%20slang%3D%22en-US%22%3EVba%20code%20with%20unprotect%20that%20worke%20on%20Excel%202010%20doesn't%20work%20on%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1357980%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20macro%20that%20worked%20on%20Excel%202010%20and%20doesn't%20work%20on%20Excel%202016.%20In%20this%20macro%20I%20protect%20the%20workbook%20and%20all%20the%20sheets%20%22Before%20close%22%20and%20unprotect%20depending%20on%20the%20user%20that%20opens%20the%20file%20on%20%22Open%22.%20The%20macro%20uses%20the%20same%20variable%20to%20protect%20and%20unprotect%20but%20when%20I%20ran%20it%20on%20Excel%202016%20it%20protected%20the%20workbook%20and%20the%20sheets%2C%20and%20unprotected%20the%20sheets%20ok%20but%20didn't%20unprotect%20the%20workbook%2C%20it%20said%20that%20the%20password%20was%20wrong.%20I've%20tried%20also%20from%20the%20menu%20and%20the%20structure%20of%20the%20workbook%20cannot%20be%20unprotected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20lines%20of%20code%20for%20protecting%20are%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Each%20ws%20In%20ActiveWorkbook.Worksheets%3CBR%20%2F%3Ews.Protect%20Password%3A%3Dclau%3CBR%20%2F%3ENext%20ws%3CBR%20%2F%3EActiveWorkbook.Protect%20Password%3A%3Dclau%2C%20structure%3A%3DTrue%2C%20Windows%3A%3DFalse%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20lines%20for%20unprotecting%20are%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20(Application.UserName%20%3D%20%22XXX%22%20Or%20Application.UserName%20%3D%20%22YYY%22)%20Then%3CBR%20%2F%3EActiveWorkbook.Unprotect%20Password%3A%3Dclau%3CBR%20%2F%3EFor%20Each%20ws%20In%20ActiveWorkbook.Worksheets%3CBR%20%2F%3Ews.Unprotect%20Password%3A%3Dclau%3CBR%20%2F%3ENext%20ws%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20error%20is%20the%201004%20and%20it%20says%20that%20is%20caused%20by%20worng%20password%20but%20%22clau%22%20is%20the%20same%2C%20I%20don't%20understand%20what's%20happening...%20What%20can%20be%20wrong%3F%20How%20can%20I%20unprotect%20the%20workbook%20now%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1357980%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Hi,

 

I have a macro that worked on Excel 2010 and doesn't work on Excel 2016. In this macro I protect the workbook and all the sheets "Before close" and unprotect depending on the user that opens the file on "Open". The macro uses the same variable to protect and unprotect but when I ran it on Excel 2016 it protected the workbook and the sheets, and unprotected the sheets ok but didn't unprotect the workbook, it said that the password was wrong. I've tried also from the menu and the structure of the workbook cannot be unprotected.

 

The lines of code for protecting are:

 

For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=clau
Next ws
ActiveWorkbook.Protect Password:=clau, structure:=True, Windows:=False

 

The lines for unprotecting are:

 

If (Application.UserName = "XXX" Or Application.UserName = "YYY") Then
ActiveWorkbook.Unprotect Password:=clau
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:=clau
Next ws
End If

 

The error is the 1004 and it says that is caused by worng password but "clau" is the same, I don't understand what's happening... What can be wrong? How can I unprotect the workbook now?

 

Thanks.

0 Replies