Protect or Unprotect Cell dependent on different cell
in [General]
|
From: David Bruce David on 25 Mar 2010 17:42 I am trying to find a way of automatically protecting a cell if an adjacent cell has a specific value whilst if that cell has a different value the cell would be unprotected. For example if I am aiming to have a column with values in that will be entered but if a field next to it has a 1 in the value field will then be locked. But if the 1 is changed to a 0 the value field could then be edited. Many Thanks for any help. David
From: Don Guillett on 25 Mar 2010 18:08 You could do this with a worksheet_change event to make it fully automatic.Unlock ccell f1>protect the sheet>right click sheet tab>view code>insert this. Now when you change f1 it will lock/unlock g1 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> Range("f1").Address Then Exit Sub With ActiveSheet ..Unprotect If Target = 1 Then .Range("g1").Locked = True Else .Range("g1").Locked = False End If ..Protect End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "David Bruce" <David Bruce(a)discussions.microsoft.com> wrote in message news:DF9BDF4D-0E59-43CC-A498-BFDF3CB2D46F(a)microsoft.com... >I am trying to find a way of automatically protecting a cell if an adjacent > cell has a specific value whilst if that cell has a different value the > cell > would be unprotected. > > For example if I am aiming to have a column with values in that will be > entered but if a field next to it has a 1 in the value field will then be > locked. But if the 1 is changed to a 0 the value field could then be > edited. > > Many Thanks for any help. > > David >
From: Jim Thomlinson on 26 Mar 2010 10:19 My preference for that is to use data validation with a custom validation. Then you can use a formula to check the values of other cells to either allow or disallow entry. Fot ease of use to your end users you may want to add a conditional format to indicate when the cell is available to be edited... Data | Validation | Custom | Add a formula and uncheck Ignore Blanks -- HTH... Jim Thomlinson "David Bruce" wrote: > I am trying to find a way of automatically protecting a cell if an adjacent > cell has a specific value whilst if that cell has a different value the cell > would be unprotected. > > For example if I am aiming to have a column with values in that will be > entered but if a field next to it has a 1 in the value field will then be > locked. But if the 1 is changed to a 0 the value field could then be edited. > > Many Thanks for any help. > > David >
|
Pages: 1 Prev: Default font for pasted text in text boxes - Excel 2007 Next: Always On Top |