If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Locking other cells based on values in a cell
| 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 |
#2
|
|||
|
|||
Locking other cells based on values in a cell
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 wrote in message ... | 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 |
#3
|
|||
|
|||
Locking other cells based on values in a cell
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 -----Original Message----- 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 wrote in message ... | 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 . |
#4
|
|||
|
|||
Locking other cells based on values in a cell
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 "Mario" wrote in message ... 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 -----Original Message----- 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 wrote in message ... | 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 . |
Thread Tools | |
Display Modes | |
|
|