Locking and Unlocking Cells

[raship]

First of all, I am very very geek for VB codes, so please excuse my poor coding attempts

let me explain what I want to achieve.


I have cell A1, which has two text values, "yes" or "no".(by data validation)
Now if value "no" is selected, then I want cells A2:A6 locked and content cleared. and if value is changed to "Yes" then those cells should be unlocked.


following macro I have entered

Sub Lockcell()

If Sheet1!a1 = "No" Then
Range("A2:A6").Select
Selection.Locked = True
Selection.FormulaHidden = False
Selection.ClearContents
End If
End Sub

Obviously, this didnt work. On searching this forum, I got something about activesheet,
and I tried with this

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveSheet.Cells(1, 1).Text = "No" Then
ActiveSheet.Range(Cells(2, 1), Cells(6, 1)).Locked = True
Else
ActiveSheet.Range(Cells(2, 1), Cells(6, 1)).Locked = False
End If
End Sub

Please give little guidance if active.sheet command to be used.

 

[hippiehacker]

the locked/unl;ocked state makes just sense if you password protect the sheet

 

[raship]

the locked/unl;ocked state makes just sense if you password protect the sheet

Being noob, I know about that.

The above listed thing is my only requirement. I will appreciate if you reply to my above mentioned query.

 

[hippiehacker]

the activesheet command don't need to be used on the Workbook Module like Private Sub Worksheet_Change because the code specified in there only runs in this worksheet

if you have a normal module and you want to process some code on the active sheet then you could use the ActiveSheet command if the active sheet is the sheet you want to look through

 

[raship]

the activesheet command don't need to be used on the Workbook Module like Private Sub Worksheet_Change because the code specified in there only runs in this worksheet

if you have a normal module and you want to process some code on the active sheet then you could use the ActiveSheet command if the active sheet is the sheet you want to look through

Thanks for reply. I appreciate this. Now problem is I know about excel. But I know very little about VB codes. So whatever you said in your last reply is roman and Greek.
But I will try to understand. Should I put that code in module?
can you give what code will achieve my above mention result?
Thanks