Protection password changes

manojrf

Board Regular
Joined
Mar 28, 2011
Messages
107
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
is that in the workbook_open event?
 
Upvote 0
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.
 
Upvote 0
put this in a copy of your file in the ThisWorkbook location

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top