Lock Column Cells If Cell Equals

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using this worksheet https://www.box.com/s/q6ulkp0zp2ycxo0utgfa to record staff resource.

As you can see in column B, there is a value of "Enter your name", which instructs the user where to start entering their information. Then, when the user enters their details in this row, the next row is popluated with the pre-defined text.

and this is the piece of code in my script which creates this value:

With Target
Select Case True

Case .Column = 2
If .Value2 <> "Enter your name" And .Offset(, -1) = "" Then

Set FirstBlankCell = Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
FirstBlankCell.Value = "Enter your name"
End If
Case Else
End Select
End With
Unfortunately I have some users who are unable to follow such a simple instruction and start to enter their details into any row.

Could someone perhaps tell me please, how I may go about adapting this so that any rows in column B after this text value are locked, thus forcing the user to enter the details where I would like them to start.

Many thanks and kind regards

Chris

[Simon Lloyd]

Ive not looked at your workbook as i'm not one for downloading from other sources, you can attach workbooks here, anyway, "locking" a column requires that the worksheet be protected, you'd have to start with all cells unlocked, then check for a value and then lock the offset something likeActivesheet.Unprotect Password:= "password"
if Range("B" & rows.count).end(xlup).offset(0,1)="" then
Range("B" & rows.count).end(xlup).offset(0,1).locked = True
else
Range("B" & rows.count).end(xlup).offset(0,1).locked=false
end if
Activesheet.Protect Password:= "password"
not test just written off the cuff.

[hobbiton73]

Hi @Simon Lloyd, thank you for taking the time to read and reply to my post. I also completely understand your point about downloading files.

I've initially set all the cells in my column B range "Staff" to unlocked. I've then protected the sheet.

I've tried to incorporate your suggestion, but unfortunately the cells remain unlocked. But I have to admit I'm not sure whether I've implemented it correctly.

I've added my full script below. Could you possibly look at this please and let me know where I'm going wrong?

Option Explicit
Public preValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range, res As Variant
Dim FirstBlankCell As Range
Dim lr As Long
Dim msg
Dim rCell As Range
Dim Rng As Range, Dn As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range
Dim Rw As Range


If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndNow
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.EnableCancelKey = xlDisabled
lr = lr

Application.EnableCancelKey = xlDisabled
Sheets("Input").Protect "password", UserInterfaceOnly:=True, AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

With Target
Select Case True

Case .Column = 2
If .Value2 <> "Enter your name" And .Offset(, -1) = "" Then

Set FirstBlankCell = Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
FirstBlankCell.Value = "Enter your name"
End If
Case Else
End Select
End With

If Range("Staff" & Rows.Count).End(xlUp).Offset(0, 1) = "" Then
Range("Staff" & Rows.Count).End(xlUp).Offset(0, 1).Locked = True
Else
Range("Staff" & Rows.Count).End(xlUp).Offset(0, 1).Locked = False
End If




With Target
Select Case True

Case .Column = 2
If .Value2 > 0 And .Value2 <> "Enter your name" And .Offset(, -1) = "" Then
.Offset(, 1).Value2 = "Yes"
.Offset(, 2).Value2 = "--Select--"
.Offset(, 3).Value2 = "--Select--"
.Offset(, 4).Value2 = "--Select--"
.Offset(, 5).Value2 = "Enter your FTE"
.Offset(, 6).Value2 = "C&R"
.Offset(, 7).Value2 = "--Select--"
.Offset(, 17).Value2 = "Enter the name of your Line Manager"
End If
Case Else
End Select
End With

With Target
Columns("A:S").EntireColumn.AutoFit
End With

EndNow:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.EnableCancelKey = xlInterrupt


End Sub
Many thanks and kind regards

Chris