Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional unlock
Hi
Is it possible to unlock a cell dependent on a specific value being entered in anther cell in the same row? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional unlock | Excel Discussion (Misc queries) | |||
Unlock VBE | Excel Discussion (Misc queries) | |||
How to Unlock | New Users to Excel | |||
Cannot unlock excel | Excel Discussion (Misc queries) | |||
how do i password protect an .xls file? how do i unlock it for automation. e.g. want to unlock and access a .xls from another .xls macro. | Excel Worksheet Functions |