Restrict entries in cells

[megnin]

If an entry is made in C2 I don't want any entry to be made in F2 until the workbook is closed and opend again.

How would I turn on Cell Validation for F2 when an entry is made in C2, but only during the current "session"?

Once the file is exited and saved then opened again, F2 could be edited normally.

Thanks!

 

[lenze]

A couple of questions for clarification.

When the workbook is reopened, what if a change is made to C2? Do you want F2 to be reprotected?

 

[megnin]

Not necessary. The file is a log. Once data in C2 is entered it is not changed.

 

[phantom1975]

Here's how you can do it. Put this code in the worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address = "$F$2" Then
If Range("F2").Value = "RESTRICTED" Then
MsgBox "You may not enter data here until you exit this workbook.", vbOKOnly + vbExclamation, "Access Denied"
Range("A1").Activate
End If
End If
End Sub

Then put this code in the ThisWorkbook module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address = "$F$2" Then
If Range("F2").Value = "RESTRICTED" Then
MsgBox "You may not enter data here until you exit this workbook.", vbOKOnly + vbExclamation, "Access Denied"
Range("A1").Activate
End If
End If
End Sub

Does this help?

 

[lenze]

Since this is a log, do you have muliple rows. Do you want the same restrictions to apply to C3 and F3, C4 and F4, etc.

 

[megnin]

Since this is a log, I need it to work for the entire C and F columns. Does that throw a monkey wrench into it?

 

[lenze]

You could do something like locking the cells in Column F and the using a WorkSheet change event that protects the WorkSheet anytime a value is entered in Column C. You would then run an On Open event to unprotect the WorkSheet

HTH - Repost if you need details

 

[megnin]

That sounds like about what I want to do. I only want to restrict entry on the same row (F column) as the new entry (C column). Other entries including entries in other rows of F column should be allowed.

Thanks for your assistance!

 

[lenze]

Here's some code to help you get started. You may have to tweak it to fit your needs.

Place this in the WorkSheet Module
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
Target.Offset(0, 3).Locked = True
ActiveSheet.Protect
End Sub

Place this in the ThisWorkbook module
Private Sub Workbook_Open()
ThisWorkbook.Sheets("Sheet1").Unprotect
Application.ScreenUpdating = False
Dim c As Range
Dim myColumn As Range
Range("F:F").Select
Set myColumn = Selection
For Each c In myColumn
If c.Locked = True Then c.Locked = False
Next
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Adjust as needed.

HTH

 

[megnin]

lenze, thank you very much!!