ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Block cell if specific condition exists (https://www.excelbanter.com/excel-discussion-misc-queries/224347-block-cell-if-specific-condition-exists.html)

WildWill
Block cell if specific condition exists
 
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[_90_]
Block cell if specific condition exists
 
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
======
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"WildWill" wrote:

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.

WildWill
Block cell if specific condition exists
 
Awesome! Can please explain the "...in the Sheet Module..." bit to me - how
do i do this?

"JBeaucaire" wrote:

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
======
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"WildWill" wrote:

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.

Jarek Kujawa[_3_]
Block cell if specific condition exists
 
right click on the Sheet tab-select View code
this is how you get there


U¿ytkownik "WildWill" napisa³ w
wiadomo¶ci ...
Awesome! Can please explain the "...in the Sheet Module..." bit to me -
how
do i do this?

"JBeaucaire" wrote:

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
======
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"WildWill" wrote:

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.



WildWill
Block cell if specific condition exists
 
I get "Compile Error: Syntax Error" in a Microsoft Visual basic window?

"Jarek Kujawa" wrote:

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


U¿ytkownik "WildWill" napisa³ w
wiadomo¶ci ...
Awesome! Can please explain the "...in the Sheet Module..." bit to me -
how
do i do this?

"JBeaucaire" wrote:

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
======
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"WildWill" wrote:

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[_90_]
Block cell if specific condition exists
 
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
======

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"WildWill" wrote:

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

"Jarek Kujawa" wrote:

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


U¿ytkownik "WildWill" napisa³ w
wiadomo¶ci ...
Awesome! Can please explain the "...in the Sheet Module..." bit to me -
how
do i do this?

"JBeaucaire" wrote:

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
======
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"WildWill" wrote:

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.




WildWill
Block cell if specific condition exists
 
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?

"JBeaucaire" wrote:

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
======

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"WildWill" wrote:

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

"Jarek Kujawa" wrote:

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


U¿ytkownik "WildWill" napisa³ w
wiadomo¶ci ...
Awesome! Can please explain the "...in the Sheet Module..." bit to me -
how
do i do this?

"JBeaucaire" wrote:

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
======
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"WildWill" wrote:

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.




WildWill
Block cell if specific condition exists
 
IT WORKS!!! Thanks everyone!

"WildWill" wrote:

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?

"JBeaucaire" wrote:

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
======

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"WildWill" wrote:

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

"Jarek Kujawa" wrote:

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


U¿ytkownik "WildWill" napisa³ w
wiadomo¶ci ...
Awesome! Can please explain the "...in the Sheet Module..." bit to me -
how
do i do this?

"JBeaucaire" wrote:

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
======
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"WildWill" wrote:

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.




Chris Bode
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        If Sheet1.Cells(Target.row, 1).Value = "Failed" Then
            Sheet1.Cells(Target.row + 1, 1).Select
        End If
    End If
End Sub
Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com


All times are GMT +1. The time now is 07:14 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com