CAN WE LOCK CELLS AT F COLUMN BASED ON VALUE AT C COLUMN

[Eddy Stan]

HI ALL,

' at start all cells in range c5:c1000 will be 0
' wish to lock cell f5:f1000, when c5:c1000 is 1
' wish if f5:f1000 access itself is not possible when c5:c1000 is 1


Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range("c5:c1000")

If Target.cell(0, 3) = 1 Then

Target.Cells.Offset(0, 3).Locked = True

Else

Target.Cells.Offset(0, 3).Locked = False

End If

End Sub

PL HELP

 

[joel]

Why don't you use datavalidation with the option set to custom. then
you can put in any formula you want including references to other cells.

 

[Ryan H]

Put this macro in the sheet module. This macro will scan ech cell in range
C5:C1000 looking for values not equal to 1. If it finds a value other than 1
it unprotects the worksheet.

In order to lock cells you have to highlight the cells you wish to lock,
right click the range, then under the protection tab, click the Lock
checkbox. The cells will not become locked untill you protect the sheet.

This code protects the sheet and unprotects the sheets according to values
in C5:C1000. So make sure range F5:F1000 is set to be locked when the sheet
is protected. And unlock cells that you want the users to enter data in.
Hope this helps! If so, let me know, click "YES" below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

With Sheets("Sheet1")

' scan each cell in range to test if it = 1
For Each rng In .Range("C5:C1000")
If rng.Value <> 1 Then
.Unprotect
Exit Sub
End If
Next rng

' lock cells
.Range("F5:F1000").Locked = True

' protect sheet so Col. F is locked
.Protect
End With

End Sub

 

[Eddy Stan]

Dear Mr Ryan,

Your code has protected all cells
i changed your code a little bit as below
' b5 is date, i want if b5 is in b7:b1000 then it must allow change in
' g7:j1000, else should not allow, as the dates could be prior to date in b5
' which means i like to protect data entered prior to date in b5
' suppose $b$5 has 27-01-2010 then when
' b12 has 26-01-2010 - dont allow to change in g12:j12 but
' b13 has 27-01-2010 - so allow change in g13:j13

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
With Sheets("Biz FT Daily")
' scan each cell in range to test if it = 1
For Each rng In .Range("B7:B1000")
If rng.Value = Range("b5") Then
..Unprotect
Else
..Protect
Exit Sub
End If
Next rng

' lock cells
.Range("G7:J1000").Locked = True

' protect sheet so Col. F is locked
..Protect
End With

End Sub

pl help me out, thanks in advance.

 

[Ryan H]

Yes, by default Excel has all cells set to be locked if the worksheet is
protected. Thats why I said you will have to unlock cells that users need to
type in. There is no way to lock cells without protected the worksheet.

My code will work for you with the criteria you asked for in your original
post. Now you have totally new criteria. This new code below will work with
the new criteria I think you gave.

To explain. This code will test if the Target is within B7:B1000. If it
is, the code will scan each cell in B7:B1000 testing if it is a date and if
it is less than B5. If the cell is less that B5 then the adjacent cells in
Col. G thru Col. J are locked else they are unlocked. For example, if B10 is
less than B5 then G10:J10 is locked. Note make sure that the cells you want
the user to type in are unlocked. I would suggest unlocking the whole sheet.
Thus only Cols. G thru Cols. J will be locked.

Hope this helps! If so, let me know, click "YES' below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range
Dim rng As Range

With Sheets("Biz FT Daily")

' test if Target is in Range B7:B1000
Set MyRange = Application.Intersect(.Range("B7:B1000"), Target)

' if Target is in range run loop
If Not MyRange Is Nothing Then

' unprotect sheet so locked property can be changed
.Unprotect

' scan each cell in range to test if it = 1
For Each rng In .Range("B7:B1000")

' test if rng value is a date
If IsDate(rng.Value) Then

' lock or unlock cells dependent on B5
If rng.Value < .Range("B5") Then
.Range(.Cells(rng.Row, "G"), .Cells(rng.Row,
"J")).Locked = True
Else
.Range(.Cells(rng.Row, "G"), .Cells(rng.Row,
"J")).Locked = False
End If
End If
Next rng
End If

' protect sheet so cells selected to be locked will lock
.Protect
End With

End Sub

 

[Eddy Stan]

Dear Mr Ryan,
The post helped me and though i click "Yes" still i want to thank you very
much for understanding my error in query and repling correctly.

eddy stan

 

[Ryan H]

No problem.
--
Cheers,
Ryan

Dear Mr Ryan,
The post helped me and though i click "Yes" still i want to thank you very
much for understanding my error in query and repling correctly.

eddy stan