Conditional unlock
Hi
Is it possible to unlock a cell dependent on a specific value being entered in anther cell in the same row? |
Conditional unlock
Hi,
You can doo it using a change event. This check B1 which must be unlocked and if the value changes to 999 then A1 becomes unlocked Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("B1")) Is Nothing Then If Target.Value = 999 Then ActiveSheet.Unprotect Password:="mypass" Range("A1").Locked = False Else Range("A1").Locked = True End If ActiveSheet.Protect Password:="mypass" End If End Sub Mike "johnsail" wrote: Hi Is it possible to unlock a cell dependent on a specific value being entered in anther cell in the same row? |
Conditional unlock
Hi Mike
Many thanks for the quick response. The routine "does what it says on the tin" and I have tried to adapt it to do all that I need it to do. However I have failed miserably - I have been unable to work out what is basically going on in your routine. If you are willing to help further I will spell out exactly what is required:- 1 In each row all cells (A to J) are defined as locked EXCEPT B, C & D. 2 If "X" is entered into C then E should be unlocked to allow fdata entry. 3 If any other data is entered into C then J should be unlocked for data entry. 4 If C is changed from "X" to "non X" or vice versa then the appropriate lock / unlock needs to be applied. 5 If the value in C is subsequently deleted then both E and J need to be locked. To further complicate things - whenever J is locked it needs to have the formula inserted that was in the cell to start with. My thoughts on this are to have a hidden cell (K) that contains a copy of the formula that can be copied back. I sincerely hope that you can help. Regards John "Mike H" wrote: Hi, You can doo it using a change event. This check B1 which must be unlocked and if the value changes to 999 then A1 becomes unlocked Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("B1")) Is Nothing Then If Target.Value = 999 Then ActiveSheet.Unprotect Password:="mypass" Range("A1").Locked = False Else Range("A1").Locked = True End If ActiveSheet.Protect Password:="mypass" End If End Sub Mike "johnsail" wrote: Hi Is it possible to unlock a cell dependent on a specific value being entered in anther cell in the same row? |
All times are GMT +1. The time now is 03:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com