• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Column/row locking is not working

K

kaushik03

Member
Hi all,


Following piece of code is not working while trying to lock column


Sheet1.Range("A:A").Locked = True


After running the code, once I go back and write something in Col A, I am not being prevented from doing so..


Can you plz tell me where I am doing wrong or what needs to be done?


Kaushik
 
Following code works for me:

[pre]
Code: 
Public Sub Test()
Sheet1.Range("A:A").Locked = True
Sheet1.Protect 'You need to protect sheet
End Sub
[/pre]
 
@ Shri:


If I add Sheet1.Protect, entire wokksheet will get protected (plz correct me if I am wrong).


But actually I want certain columns to be locked(or protected) based on condition.


Say, if A1=B1 then only column D should be prevented for editing, and if A1<>B1 then only column E and F should be protected/locked.


@Hi bobhc: Locked = false does not work..


Kaushik
 
Hi Shri...


I did not understand your post completely but below here is what I am trying to do:


: If condition met, then I want only certain columns to be protected not the entire sheet.


Am I clear on my requirement? Please tell me if I am not able to make my points clear.


Anyways, a very happy and prosperous "Deewali" to you my friend.


Kaushik
 
Hi Kaushik,


If you will check in any worksheet, All cells are LOCKED by default.


Select a cell and right click and choose "Format Cells" and then check "Protection" tab. Here you will see the checkbox "Locked" tickmarked (which is equivalent of setting
Code: 
Range.Locked = True in VBA). 


This locking comes in effect when we protect the sheet. Until then it carries no significance.


So place the following code in the worksheet:

[pre]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [A1:B1]) Is Nothing Then
If [A1] = [B1] Then
Sheet1.Unprotect
Sheet1.Cells.Locked = False
Sheet1.Range("D:D").Locked = True
Sheet1.Protect
Else
Sheet1.Unprotect
Sheet1.Cells.Locked = False
Sheet1.Range("E:F").Locked = True
Sheet1.Protect
End If
End If
End Sub
[/pre]
I've written the sample for case A1B1. Let me know should you need some more explanation.


Wish you a very happy and prosperous Diwali- Shrivallabha Redij
 
Hi Shri,


Thank you for such detailed explanation.


But, with this code too, excel does not prevent me from editing cells( or entering something/changing value) in Col D(when A1=B1).


Am I not able to follow your instructions?


Sorry, for stretching this post friend!


Regards,

Kaushik
 
You must log in or register to reply here.
Back
Top