Block cell if specific condition exists

[WildWill]

I have 2 cells, A1 and B1. I want to condition B1 so that if A1 contains a
specific value or text, i.e. A1="Filled", then B1 will be blocked and it will
not be possible to enter any data into B1. If A1 is any other value except
for "Filled", B1 will behave normally and will allow any data entry.

 

[JBeaucaire]

Put this formula in the Sheet Module where you want column B to be
inaccessible. If the cell in column A has "Filled" as its value, Excel will
push the cursor back out of column B every time the user tries to go there.
======
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target.Offset(0, -1).Value = "Filled" Then Target.Offset(0,
1).Select
End If
End Sub
======

 

[WildWill]

Awesome! Can please explain the "...in the Sheet Module..." bit to me - how
do i do this?

 

[Jarek Kujawa]

right click on the Sheet tab->select View code
this is how you get there

 

[WildWill]

I get "Compile Error: Syntax Error" in a Microsoft Visual basic window?

 

[JBeaucaire]

The internet puts line breaks in the code. Try this version, hopefully it
will survive forum formatting:

======
Private Sub Worksheet_SelectionChange(ByVal _
Target As Excel.Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target.Offset(0, -1).Value = "Filled" Then
Target.Offset(0, 1).Select
End If
End If
End Sub
======

 

[WildWill]

Not working - I copied and pased as provided. Perhaps my example was not a
good one, as I actually have a range of cells (e.g. E5 to E25) which need to
be restricted in this manner, and not just the hypothetical "B1" as
previously mentioned. Does this perhaps change the script to be entered?

 

[WildWill]

IT WORKS!!! Thanks everyone!

Not working - I copied and pased as provided. Perhaps my example was not a
good one, as I actually have a range of cells (e.g. E5 to E25) which need to
be restricted in this manner, and not just the hypothetical "B1" as
previously mentioned. Does this perhaps change the script to be entered?