Lock cells in a row if a date is entered into an adjacent cell

[Simplyflorida]

I have a row of data in cells A5 to P5. When a date (ie Feb 26 2013) is entered into cell Q5, I want the whole row to be locked.

Need to do this for a complete range of cells (rows 5 to 500 for example).

I need to use VBA, but am not sure of the wording.

Any ideas please.

 

[Jubjab]

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 17 Then
        ThisRow = Target.Row
            ActiveSheet.Unprotect
        If Target.Value > 0 Then
            Range("A" & ThisRow & ":P" & ThisRow).Locked = True
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        Else
            Range("A" & ThisRow & ":P" & ThisRow).Locked = False
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        End If
    End If
End Sub

Make sure you unlock all cells in the worksheet first, as cells are locked by default.

 

[DMcCallister]

Here's an idea until someone comes up with a better one...
First if you plan to have these fields not be able to be edited, unlock all the cells in the workbook first, and remove the ' in front of the lines in the below code, otherwise the Code will lock all those cells.
If you just want the cells locked remove the lines I've commented.
I'm also assuming the only value you expect in the field is a Date.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Worksheets("Sheet1").Unprotect
If Range("Q5").Value <> "" Then
Range("A5:P5").Locked = True
'Worksheets("Sheet1").Protect
Else
Range("A5:P5").Locked = False
'Worksheets("Sheet1").Unprotect
End If
End Sub

I know it's not the most efficient piece of code, but it does what you want.

 

[Simplyflorida]

Works great for row 5.

Sorry to be a pain but how do I program it to work for (say) lines 5 through to 5000 as I have lots of data. Need to lock each line individually when a date is entered into Column Q.

Thanks again for you time and help

 

[Jubjab]

did you try my solution? it is not coded for any particular row

 

[Simplyflorida]

I am working solely on row 5 at the moment but the intention is to use many rows.

I entered the following code but cells do not lock. I have data in columns A to Q and the date need to be entered in column R (column 18). When I enter a date in row R, the cells in row 5 still remain unlocked.

Cels are all unlocked to begin with.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 18 Then
ThisRow = Target.Row
ActiveSheet.Unprotect
If Target.Value > 0 Then
Range("A5:Q5").Locked = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Else
Range("A5:Q5").Locked = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End If
End Sub


Any ideas please.

 

[Jubjab]

The code is ok, so maybe you placed the code in the wrong worksheet?

 

[Simplyflorida]

Tab is called "sheet1" and the code is within this sheet. Right click tab and added code.

I will keep trying ???

 

[Jubjab]

Is the macro running at all?

 

[Simplyflorida]

Macro is working ok on row 5. This stops any new data being entered.

However, the macro is also preventing me from any formatting etc on rows 6 onward.

Is there a way just to protect row 5 and leave all other rows open to change.

As data and then dates are entered into rows 6,7,8 etc I want these protected from change but rows 9 onward open so that I can add data and new rows.