Protection password changes

[manojrf]

Hi everyone,

I have got a complicated problem with me. I have got 10 sheets in a workbook and I have protected 9 sheets with a password. On the 10th sheet, I have protected it with a different password and unprotecting / protecting macro is included in a module. When it is run, sometimes the password of the other 9 sheets gets changed to that of the 10th sheet. Or otherwise the password of the 10th sheet gets changed to that of the other 9 . I don't know how it is happening.

I use MS Excel 2013.

Can some help me out ?

The code I use is :

Sheets ("10"). Unprotect "1"

Sheets ("10"). Protect "1"

Thanks.

 

[mole999]

is that in the workbook_open event?

 

[manojrf]

Hi,

First of all thank you for sparing your time for me and let me say that I am not an expert in making macros. I think the answer to your question is no.

The macro is use is given below, which is recorded.

Sub Macro7()
'
' Subvention sheet clear
'





'Çlear contents
Sheets("SUBVENTION").Select
Range("A6:A35, F6:F35, H6:H35, K6:K35").Select
Selection.ClearContents






'Subvention sheet formula fill up
Sheets("SUBVENTION").Unprotect "1"
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(ISERROR(VLOOKUP(INDEX(LOAN!C[-1]:C[11],MATCH(RC[-1],LOAN!C[-1],0),1),LOAN!C[-1]:C[11],3)),""Non home a/c or new a/c"",VLOOKUP(INDEX(LOAN!C[-1]:C[11],MATCH(RC[-1],LOAN!C[-1],0),1),LOAN!C[-1]:C[11],3)))"
Range("B6").Select
Selection.AutoFill Destination:=Range("B6:B35"), Type:=xlFillDefault
Range("B6:B55").Select
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(ISERROR(VLOOKUP(INDEX(LOAN!C[-2]:C[10],MATCH(RC[-2],LOAN!C[-2],0),1),LOAN!C[-2]:C[10],6)),""Non home a/c or new a/c"",VLOOKUP(INDEX(LOAN!C[-2]:C[10],MATCH(RC[-2],LOAN!C[-2],0),1),LOAN!C[-2]:C[10],6)))"
Range("C6").Select
Selection.AutoFill Destination:=Range("C6:C35"), Type:=xlFillDefault
Range("C6:C55").Select
Range("K6").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-10]="""","""",VLOOKUP(RC[6]&""SB"",DEPOSIT!R1C21:DEPOSIT!R50994C24,4,0))"
Range("K6").Select
Selection.AutoFill Destination:=Range("K6:K35"), Type:=xlFillDefault
Range("L6").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-11]="""","""",IF(ISERROR(VLOOKUP(INDEX(LOAN!C[-11]:C[1],MATCH(RC[-11],LOAN!C[-11],0),1),LOAN!C[-11]:C[1],5)),""Non home a/c or new a/c"",VLOOKUP(INDEX(LOAN!C[-11]:C[1],MATCH(RC[-11],LOAN!C[-11],0),1),LOAN!C[-11]:C[1],5)))"
Range("L6").Select
Selection.AutoFill Destination:=Range("L6:L35"), Type:=xlFillDefault
Range("Q6").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-16]="""","""",IF(ISERROR(VLOOKUP(INDEX(LOAN!C[-16]:C[-4],MATCH(RC[-16],LOAN!C[-16],0),1),LOAN!C[-16]:C[-4],2)),""Non home a/c or new a/c"",VLOOKUP(INDEX(LOAN!C[-16]:C[-4],MATCH(RC[-16],LOAN!C[-16],0),1),LOAN!C[-16]:C[-4],2)))"
Range("Q6").Select
Selection.AutoFill Destination:=Range("Q6:Q35"), Type:=xlFillDefault
Range("S6").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]="""","""",IF(ISERROR(VLOOKUP(INDEX(DEPOSIT!C[-16]:C[1],MATCH(RC[-8],DEPOSIT!C[-16],0),1),DEPOSIT!C[-16]:C[1],4)),""Non home a/c or new a/c"",VLOOKUP(INDEX(DEPOSIT!C[-16]:C[1],MATCH(RC[-8],DEPOSIT!C[-16],0),1),DEPOSIT!C[-16]:C[1],4)))"
Range("S6").Select
Selection.AutoFill Destination:=Range("S6:S35"), Type:=xlFillDefault

Sheets("SUBVENTION").Protect "1"
Range("A6").Select


End Sub

All the other 9 sheets does have another password.

Thanks.

 

[mole999]

put this in a copy of your file in the ThisWorkbook location

Private Sub Workbook_Open()
    With Sheets("SUBVENTION")
        .Range("A6:A35, F6:F35, H6:H35, K6:K35").ClearContents
        'Subvention sheet formula fill up
        .Unprotect "1"
        .Range("B6").FormulaR1C1 = _
            "=IF(RC[-1]="""","""",IF(ISERROR(VLOOKUP(INDEX(LOAN!C[-1]:C[11],MATCH(RC[-1],LOAN!C[-1],0),1),LOAN!C[-1]:C[11],3)),""Non home a/c or new a/c"",VLOOKUP(INDEX(LOAN!C[-1]:C[11],MATCH(RC[-1],LOAN!C[-1],0),1),LOAN!C[-1]:C[11],3)))"
    
        .Range("B6").Select
        .Selection.AutoFill Destination:=Range("B6:B35"), Type:=xlFillDefault
        .Range("B6:B55").Select
    
        .Range("C6").FormulaR1C1 = _
            "=IF(RC[-2]="""","""",IF(ISERROR(VLOOKUP(INDEX(LOAN!C[-2]:C[10],MATCH(RC[-2],LOAN!C[-2],0),1),LOAN!C[-2]:C[10],6)),""Non home a/c or new a/c"",VLOOKUP(INDEX(LOAN!C[-2]:C[10],MATCH(RC[-2],LOAN!C[-2],0),1),LOAN!C[-2]:C[10],6)))"
        .Range("C6").Select
        .Selection.AutoFill Destination:=Range("C6:C35"), Type:=xlFillDefault
        .Range("C6:C55").Select
    
        .Range("K6").FormulaR1C1 = _
            "=IF(RC[-10]="""","""",VLOOKUP(RC[6]&""SB"",DEPOSIT!R1C21:DEPOSIT!R50994C24,4,0))"
        .Range("K6").Select
        .Selection.AutoFill Destination:=Range("K6:K35"), Type:=xlFillDefault
    
        .Range("L6").FormulaR1C1 = _
            "=IF(RC[-11]="""","""",IF(ISERROR(VLOOKUP(INDEX(LOAN!C[-11]:C[1],MATCH(RC[-11],LOAN!C[-11],0),1),LOAN!C[-11]:C[1],5)),""Non home a/c or new a/c"",VLOOKUP(INDEX(LOAN!C[-11]:C[1],MATCH(RC[-11],LOAN!C[-11],0),1),LOAN!C[-11]:C[1],5)))"
        .Range("L6").Select
        .Selection.AutoFill Destination:=Range("L6:L35"), Type:=xlFillDefault
    
        .Range("Q6").FormulaR1C1 = _
            "=IF(RC[-16]="""","""",IF(ISERROR(VLOOKUP(INDEX(LOAN!C[-16]:C[-4],MATCH(RC[-16],LOAN!C[-16],0),1),LOAN!C[-16]:C[-4],2)),""Non home a/c or new a/c"",VLOOKUP(INDEX(LOAN!C[-16]:C[-4],MATCH(RC[-16],LOAN!C[-16],0),1),LOAN!C[-16]:C[-4],2)))"
        .Range("Q6").Select
        .Selection.AutoFill Destination:=Range("Q6:Q35"), Type:=xlFillDefault
    
        .Range("S6").FormulaR1C1 = _
            "=IF(RC[-8]="""","""",IF(ISERROR(VLOOKUP(INDEX(DEPOSIT!C[-16]:C[1],MATCH(RC[-8],DEPOSIT!C[-16],0),1),DEPOSIT!C[-16]:C[1],4)),""Non home a/c or new a/c"",VLOOKUP(INDEX(DEPOSIT!C[-16]:C[1],MATCH(RC[-8],DEPOSIT!C[-16],0),1),DEPOSIT!C[-16]:C[1],4)))"
        .Range("S6").Select
        .Selection.AutoFill Destination:=Range("S6:S35"), Type:=xlFillDefault
        .Protect "1"
    End With
End Sub

I'm sure the fill can be improved, though what I have used before dosen't seem to flow, so i have left the selects in