Run a code in a protected sheet

[appz]

I am using a drop down box which specifes different amortization frequency. That goes to another page and calculates it.
It worked fine, but now I have to protect the calcuation sheet before I send it out.
This creates issues as now that amortization freq. calc macro does not work. Below is my code.


Sub DropDown706_Change() ' Paydown dropdown
Application.DisplayAlerts = False
Worksheets("Detail").Unprotect Password:="Inve$t1" ' Unprotects the sheet
Worksheets("Detail").Range("A38:I402,Q32:AB44").Locked = False

Dim lngLast_Row As Long
Dim lngRow As Long

lngLast_Row = Worksheets("Detail").Range("B" & Rows.Count).End(xlUp).Row

'Dropdown selections
Select Case Worksheets("Detail").Range("AmortizationPeriodDropDown")

Case "1" ' Monthly Cashflow
Application.DisplayAlerts = False
Worksheets("Detail").Unprotect Password:="Inve$t1" ' Unprotects the sheet
Worksheets("Detail").Range("B38:B398").Value = "" 'Converts the value to null
Worksheets("Detail").Range("B39:B398").FormulaR1C1 = Formual"


Just have inculded one case.
I am trying to unprtoect the Details tab using
Worksheets("Detail").Unprotect Password:="Inve$t1" ' Unprotects the sheet

But some how it does not work.

Appreaciate you help.
Thanks
Appz

 

[Jonmo1]

Welcome to the board..

You can use code to UNprotect the sheet, then do stuff, then REprotect it when done..

something like

Sheets("Detail").Unprotect "passwordgoeshere"
 
'rest of your code here
 
Sheets("Detail").Protect "passwordgoeshere"

 

[appz]

wow that works. I thought we have to include the word password: in the protect and unprotect code.
WOnderful, I feel so stupid wasting half day searching online.
Thanks jonmo1

Cheers
Appz

 

[appz]

Hmmm.. NO it unprotects and protects it back so this time now it is giving the same error.
So this is the code

Sub DropDown706_Change() ' Paydown dropdown
Application.DisplayAlerts = False
Worksheets("Detail").Unprotect "Inve$t1" ' Unprotects the sheet
Worksheets("Detail").Range("A38:I402,Q32:AB44").Locked = False

Dim lngLast_Row As Long
Dim lngRow As Long

lngLast_Row = Worksheets("Detail").Range("B" & Rows.Count).End(xlUp).Row

'Dropdown selections
Select Case Worksheets("Detail").Range("AmortizationPeriodDropDown")

Case "1" ' Monthly Cashflow

Worksheets("Detail").Range("A38:I402,Q32:AB44").Locked = True
Worksheets("Detail").Protect "Inve$t1" ' Unprotects the sheet
End Sub

So I have asked it to protect it again.
Then now why is this giving the same error.

Hmmm

 

[Jonmo1]

wow that works. I thought we have to include the word password: in the protect and unprotect code.

You don't HAVE to...
But if you're going to specify other parameters in the protect/unprotect line, then you would have to.
But since the password is the only parameter specified, and it happens to be the FIRST parameter of a protect/unprotect code
And the other parameters are optional, then it's not required.

FYI, it would be written like

Sheets("sheetname").Unprotect Password:="passwordgoeshere"

Thanks jonmo1

Cheers
Appz

Glad to help, thanks for the feedback.

 

[Jonmo1]

Do you have any Event driven code that reprotects the sheet after you make changes..?
Like
Private Sub WorkSheet_Change(ByVal Target As Range)


Also, that's not your entire code...
Please post the ENTIRE code.
And use code tags

without the # symbols below..
[#code]
Your code here
[#/code]

 

[appz]

Yes jonmo1. I did ask at the end of the sub to protect it back.

So it works fine in the first try. But when I change the fre. type, i.e in code it will change the case. I tried puttin the unprotect in the each case.

Here is my code.

Sub DropDown706_Change() ' Paydown dropdown

[Worksheets("Detail").Unprotect "Inve$t1" ]' Unprotects the sheet
[Worksheets("Detail").Range("A38:I402,Q32:AB44").Locked = False]

Dim lngLast_Row As Long
Dim lngRow As Long

lngLast_Row = Worksheets("Detail").Range("B" & Rows.Count).End(xlUp).Row]

'Dropdown selections
[Select Case Worksheets("Detail").Range("AmortizationPeriodDropDown")]

[Case "1"] ' Monthly Cashflow
]Worksheets("Detail").Unprotect Password:="Inve$t1" ' Unprotects the sheet
Worksheets("Detail").Range("B38:B398").Value = "" 'Converts the value to null
Worksheets("Detail").Range("B39:B398").FormulaR1C1 = "Formula"]

[Case "2"] ' Quaterly Cashflow
[Worksheets("Detail").Unprotect Password:="Inve$t1" ' Unprotects the sheet
Worksheets("Detail").Range("B38:B398").Value = "" 'Converts the value to null]
'Loops from month 3
[ For lngRow = 41 To lngLast_Row Step 3
Worksheets("Detail").Activate
Range("b41:b398").Select
Range("B41").Activate
ActiveSheet.Cells(lngRow, 2).FormulaR1C1 = "Formula"]

[Case "3"]
[Worksheets("Detail").Unprotect Password:="Inve$t1" ' Unprotects the sheet]
[Worksheets("Detail").Range("B38:B398").Value = "" 'Converts the value to null
For lngRow = 44 To lngLast_Row Step 6
Worksheets("Detail").Activate
Range("b44:b398").Select
Range("B44").Activate
ActiveSheet.Cells(lngRow, 2).FormulaR1C1 = "Formula"
Next]

[Case "4" ]' Yearly Cashflow
[Worksheets("Detail").Unprotect Password:="Inve$t1" ]' Unprotects the sheet
[Worksheets("Detail").Range("B38:B398").Value = "" ]
[ For lngRow = 50 To lngLast_Row Step 12
Worksheets("Detail").Activate
Range("b50:b398").Select
Range("B50").Activate
ActiveSheet.Cells(lngRow, 2).FormulaR1C1 = Formua]' not included
[Next]

[End Select
Worksheets("Detail").Range("A38:I402,Q32:AB44").Locked = True
Worksheets("Detail").Protect "Inve$t1" ]' Protects the sheet
End Sub

So hope I am doing the code tag as you have asked.
But so to summarise. The 1st tiem the change event is executed it works properly. i.e. goes and unprotects, calculates and protects it back again.
But when I change the selection in drop down box, it gives me msgbox that the sheet is protected and does not work.
So I thoght what if put the unprotect in the case and see. But sadly it does not work.
Appreciate your help.
I am newbie. So bear with me.

Thanks
Appz

 

[Jonmo1]

Is there any OTHER event driven code that might be reprotecting the sheet?

Put a break point at the beginning of that code
Put your cursor on the first line that is unprotecting the sheet, and press F9
This puts a red dot on the bar to the left.
Then run the code
And press F8 repeatedly to Step through the code and observe what's happening.
Does it jump over to another macro at any point?


To use Code tags, you only write 2 set's of tags, and all code goes between..
Remove the # symbols, they are there so you can actually see the code tags...
[#Code]
Sub Test()
'Have some code here
'More lines here etc..
End sub
[#/Code]

This will appear like this

Sub Test()
'Have some code here
'More lines here etc..
End sub

 

[appz]

Ok.
Hmm.. It does goes to unprotect line when I put a breakpoint.
every time I change the select case.
Ok here is my full code

Sub DropDown706_Change() ' Paydown dropdown

Worksheets("Detail").Unprotect "Inve$t1" ' Unprotects the sheet
Worksheets("Detail").Range("A38:I402,Q32:AB44").Locked = False

Dim lngLast_Row As Long
Dim lngRow As Long
    
lngLast_Row = Worksheets("Detail").Range("B" & Rows.Count).End(xlUp).Row

'Dropdown selections
Select Case Worksheets("Detail").Range("AmortizationPeriodDropDown")

Case "1" ' Monthly Cashflow

Worksheets("Detail").Range("B38:B398").Value = "" 'Converts the value to null
Worksheets("Detail").Range("B39:B398").FormulaR1C1 =
""
Case "2" ' Quaterly Cashflow
Worksheets("Detail").Range("B38:B398").Value = "" 'Converts the value to null
 
    'Loops from month 3
  For lngRow = 41 To lngLast_Row Step 3
       Worksheets("Detail").Activate
   Range("b41:b398").Select
   Range("B41").Activate
        ActiveSheet.Cells(lngRow, 2).FormulaR1C1 = ""        
    Next
   Worksheets("Lender's View").Activate
Case "3" ' Semmi Annual Cashflow
Worksheets("Detail").Range("B38:B398").Value = "" 'Converts the value to null
  For lngRow = 44 To lngLast_Row Step 6
        Worksheets("Detail").Activate
   Range("b44:b398").Select
   Range("B44").Activate
        ActiveSheet.Cells(lngRow, 2).FormulaR1C1 = ""       
    Next
Case "4" ' Yearly Cashflow

Worksheets("Detail").Range("B38:B398").Value = "" 'Converts the value to null
  For lngRow = 50 To lngLast_Row Step 12
        Worksheets("Detail").Activate
   Range("b50:b398").Select
   Range("B50").Activate
        ActiveSheet.Cells(lngRow, 2).FormulaR1C1 = ""        
    Next
Worksheets("Lender's View").Activate
End Select
Worksheets("Detail").Range("A38:I402,Q32:AB44").Locked = True
Worksheets("Detail").Protect "Inve$t1" ' Protects the sheet
End Sub
[Code]
 
Appreciate you help

 

[Jonmo1]

Ok.
Hmm.. It does goes to unprotect line when I put a breakpoint.
every time I change the select case.

And did you step through the code by pressing F8 to run the code 1 line at a time..And observe what happens?


You left off the / on the Closing code

[#code]
code here
[#/code]