Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. Thanks Mario |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking other cells based on values in a cell.
Mario,
This checks the two cells specified in the first IF statement only You will want to format all cells that you wish to reamain unlocked as the ActiveSheet.Protect affects the entire sheet. The default for a cell is to be locked if the sheet if protected, hence all cells will be protected unless you change the formatting. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Or Target.Address = "$A$5" Then If Target = "Plumb" Then ActiveSheet.Unprotect Target.Offset(0, 1).Cells.Locked = True Target.Offset(0, 2).Cells.Locked = True Target.Offset(0, 3).Cells.Locked = True ActiveSheet.Protect Else ActiveSheet.Unprotect Target.Offset(0, 1).Cells.Locked = False Target.Offset(0, 2).Cells.Locked = False Target.Offset(0, 3).Cells.Locked = False ActiveSheet.Protect End If End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking a cell based on a cells content | Excel Discussion (Misc queries) | |||
Excel Locking cells based on value in another cell | Excel Discussion (Misc queries) | |||
locking cells based on results at runtime | Excel Worksheet Functions | |||
Modify Row & Cell Contents based upon Cells Values | Excel Worksheet Functions | |||
Modify Row & Cell Contents based upon Cells Values | Excel Worksheet Functions |