Restrict entries in cells

megnin

Active Member
Joined
Feb 27, 2002
Messages
340
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!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Active Member
Joined
Feb 27, 2002
Messages
340
Not necessary. The file is a log. Once data in C2 is entered it is not changed.
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

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

Active Member
Joined
Feb 27, 2002
Messages
340
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

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

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

Active Member
Joined
Feb 27, 2002
Messages
340
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top