| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
|
|||
|
|||
|
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
------ Convert your Excel spreadsheet into an online calculator. http://www.spreadsheetconverter.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002: Can I condition format block of cells ? | Excel Discussion (Misc queries) | |||
| Is there a way to color a cell if it meets specific condition | Excel Discussion (Misc queries) | |||
| How to Add columns if a condition exists | Excel Discussion (Misc queries) | |||
| Repeat cell data for specific condition | Excel Discussion (Misc queries) | |||
| How to count occcurence of specific text block in a column | Excel Discussion (Misc queries) | |||
Linear Mode
