Locking other cells based on values in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

| A| B| C| D|
------------------------------------------
1|Task |Desc |Hrs |Date |
2|Stucco |
3|Paint |
4|Plumb |

Lets say above is my sample spread sheet. How can I
block/lock the cells for col B, Col C and Col D in a row
if the value in Col A of same row is not "Plumb".

In other words if A2 is not equal to "Plumb" then cells
B2, C2 and D2 has to be locked and made not editable or
non-data-enterable.


Please help

Thanks

Thanks
Mario
 
This function will lock or unlock cells in columns B-D when the value in A
is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rColAChanged As Range
Dim cCell As Range

If Not Intersect(Target, Range("A:A")) Is Nothing Then
ActiveSheet.Unprotect
Set rColAChanged = Intersect(Target, Range("A:A"))
For Each cCell In rColAChanged
cCell.Offset(0, 1).Resize(1, 3).Locked = (cCell.Value <>
"Plumb")
Next
ActiveSheet.Protect
End If
End Sub

It would be nice if this sort of thing could be done as a conditional
format. Anyone have a better way to do this?

Dave
 
I have a question on the protection now. Do I have to
protect the entire worksheet before I can use the above
sub-routine.

I also have another question. How can I selectively
unlock or unprotect certain cells so that users can enter
data only in certain cells., where as the rest of the
work sheet is protected.

one Final question. I have a button linked to a macro.
When the button is clicked the macro does some
calculations using advanced filters in another part of
the worksheet and returns the results to a cell near the
button. If I lock the work sheet, then run the macro (by
clicking the button) Excel throws an error saying the
sheet is protected and has to be unprotected.

Please help
 
The function unprotects the sheet, determines if the selected cell(s)
require locking of BCD and then protects the sheet, so you only need to
protect the sheet if you unprotect it manually. Even in that event, the
first time you make a change it column A, it will protect it automatically.

You might however need to prime the pump (so to speak) by causing the cells
in column A to be changed to trigger the function. To do this, copy column
A and paste it over column A (actually you might just want to copy and paste
the part with values as it could take a while to do the whole column). This
will cause the function to relock the cells in BCD as required.

To unprotect cells, unprotect the sheet, select the cells you wish to
unlock, then reprotect the sheet. However, the function may relock the cell
if it's in B C or D as changes are made. As written there is no way around
this.

The target cell is likely locked. You can unlock it as above but again the
function may relock it.

If the area you need the function to cover includes all of ABCD then you can
just unlock all cells on the sheet, then prime the pump as above. You'll
then need to lock any additional cells you need locked.

If the area you need the function does not include all of ABDC, you can
adjust the fuction by replace the text "A:A" with the range of column A to
monitor (e.g. "A2:A25") in both places it appears in the code. Then unlock
all cells, prime the pump and relock other cells as needed.

A more general solution that occurs to me would be to use a second hidden
sheet as a locking map. You could then place flags on the map to indicate
whether matching cells on the first sheet should be locked. These flags
could be generated by formulas yielding condional locking. The check to
lock a cell would be made as it becomes selected. However, this wouldn't be
efficient for large selections (whole columns say), and I'm not sure if you
really need a solution this general or if there isn't an easier or better
way to accomplish much the same thing.

HTH
 
Back
Top