lucking a cell automatically ...

[confused guy and desperately in need !]

Hi again

dose any one know how is possible to luck the cell automatically and
conditionally? i mean not by using the normal cell fomat option but forcing
the cell to be lucked when certain conditions meet?

any suggestion at all ?
Tks so much
M.M

 

[Gary''s Student]

Try this worksheet event macro:

Private Sub Worksheet_Calculate()

Set b9 = Range("B9")
Set a1 = Range("A1")

If b9.Value = 1 Then
Application.EnableEvents = False
b9.Locked = False
a1.Locked = True
ActiveSheet.Protect Contents:=True
Application.EnableEvents = True
End If
End Sub

If the value of B9 becomes 1, then cell A1 becomes locked.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Mike H]

Hi,

Slightly more involved but try this. Select the cells you want this to work
for which in the case of my example are A1 - A10 and then
Format|cells|protection
remove the locked checkmark
Then
Tools Protection and protect the sheet with "mypass" as a passowrd (change
to suit but it must match the password in the code).

Right click the sheet tab, view code and paste this in and the first time
you enter xxx in a1 to A1 - A10 those cells also become protected.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
If Target.Value = "xxx" Then
ActiveSheet.Unprotect Password:="mypass"
Target.Locked = True
ActiveSheet.Protect Password:="mypass"
End If
Application.EnableEvents = True
End If

End Sub

Mike

 

[confused guy and desperately in need !]

Thanks again so much dear Mike

Best wishes,
M.M

Hi,

Slightly more involved but try this. Select the cells you want this to work
for which in the case of my example are A1 - A10 and then
Format|cells|protection
remove the locked checkmark
Then
Tools Protection and protect the sheet with "mypass" as a passowrd (change
to suit but it must match the password in the code).

Right click the sheet tab, view code and paste this in and the first time
you enter xxx in a1 to A1 - A10 those cells also become protected.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
If Target.Value = "xxx" Then
ActiveSheet.Unprotect Password:="mypass"
Target.Locked = True
ActiveSheet.Protect Password:="mypass"
End If
Application.EnableEvents = True
End If

End Sub

Mike

 

[confused guy and desperately in need !]

Thanks so much dear Gary

that was very useful...

Best wishes,
M.M

 

[IanKR]

dose any one know how is possible to luck the cell automatically and

conditionally? i mean not by using the normal cell fomat option but
forcing
the cell to be lucked when certain conditions meet?

[language alert] what do you mean by "luck" the cell?