Solved: Excel Sheet Protection

[tools]

Hi all

I wanted to protect my sheet using vba.
This is the code that I have written .


ActiveSheet.Unprotect
Range("C6:C65536").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect

This works fine, now the problem is that after running this code the particular range gets protected but if the sheet contains data in another column then i am unable to edit that data.

I want only the column C to be locked and other columns to be editable
I have attached a file which contains that macro.

Thanks & Regards

[rory]

By default, cells are locked, you should initially unlock them:


ActiveSheet.Unprotect
activesheet.cells.locked = False
With Range("C6:C65536")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect

[tools]

Thanks rory :)

[Simon Lloyd]

Sorry just a slight amendment so that you are only locking the cells you really need to:

ActiveSheet.Unprotect
Activesheet.cells.locked = False
With Range("C6:C" & Range("C" & Rows.Count).End(xlUp).Row)
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect

[tools]

When I supply the column location dynamically then only the last column gets protected.




ActiveSheet.Unprotect
ActiveSheet.Cells.Locked = False

Dim colarr()
Dim rowarr()
colarr(0)="C"
colarr(1)="P"
rowarr(0)="6"
rowarr(1)="6"

For i=0 to UBound(colarr)

With Range(colarr(i) + rowarr(i) + ":" + colarr(i) & Range(colarr(i) & Rows.count).End(xlUp).Row)
.Locked = True
.FormulaHidden = False
End With

Next

ActiveSheet.Protect


Thanks

[rory]

There seems to be some code missing from what you posted, because that should not even run. If it did, you should end up with C and P cells only protected - was that what you meant?

[tools]

yes you are right only P and C cells should get protected but when i run it
only the P cell gets protected.

Thanks

[rory]

What is the full code you are actually using? And do you have data in both columns?

[tools]

I am using SOAP messaging

Here is the actual code



ActiveSheet.Unprotect
ActiveSheet.Cells.Locked = False

With Range((env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionColumn").Value)
+ (env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionRow").Value)
+ ":" +
(env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionColumn").Value)
& Range(env.Parameters.Item(0).Nodes.Item(j).Nodes.ItemByName("attributePositionColumn").Value
& Rows.count).End(xlUp).Row)
.Locked = True
.FormulaHidden = False
End With

ActiveSheet.Protect


i did put an alert to check in the values
i got the values as
B6:B...
R6:R..
P6:P..

of which the column appearin last gets protected other columns remain unprotected

[rory]

There doesn't appear to be a loop there. If that code is within a loop, then your problem is this line:
ActiveSheet.Cells.Locked = False
would be unlocking all the cells you just locked.

[tools]

Yes it is in a loop, when I removed that line the entire sheet got blocked.

Thanks

[tools]

I did use the line before the loop once and its working fine

Thanks rory