Excel VBA Lock/Unlock by Adjacent Cell Condition 2

[phoenix79]

I am trying to get two allow the user to enter values into either one cell or another.

If a user inputs any value into a cell ranging from E5 to E44 the adjacent cell in the column F locks. If a user inputs data into a cell ranging from F5 to F44 the adjacent cell in E is locked.

The locked cell also needs to unlock if data is removed from the cell that caused its lock.

I played around with code found here

http://answers.google.com/answers/threadview/id/728228.html

but I could not get it to work fully.

Help would be greatly appreciated. Thanks.

 

[Gavona]

Use data validation. For XL2003;

In F12:
Data,Validation
Allow: Custom
Ignore Blank: unticked
Formula: =isblank(E12)

In E2 do similar but:
Formula: =isblank(F12)

OR use Worksheet_Change event to detect a change in the cell range, uprotect the worksheet, lock the adjacent cell, protect the workbook.


Gavin

 

[phoenix79]

Ok feel quite stupid now. Thanks for the help. Here is the working code.

Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed
If Target.Cells.Count > 1 Then Exit Sub

' Only activate if in area with our special values
If Intersect(Target, Range("E5:F44")) Is Nothing Then Exit Sub

Application.EnableEvents = False
ActiveSheet.Unprotect

'IF Row E has data lock cell F next to it
If Target.Column = 5 And IsEmpty(Target) = False Then
ActiveSheet.Cells(Target.Row, Target.Column + 1).Locked = True
End If

'IF data in Row E is empty unlock cell F next to it
If Target.Column = 5 And IsEmpty(Target) = True Then
ActiveSheet.Cells(Target.Row, Target.Column + 1).Locked = False
End If

'IF Row F has data lock cell E next to it
If Target.Column = 6 And IsEmpty(Target) = False Then
ActiveSheet.Cells(Target.Row, Target.Column - 1).Locked = True
End If

'IF data in Row F is empty unlock cell E next to it
If Target.Column = 6 And IsEmpty(Target) = True Then
ActiveSheet.Cells(Target.Row, Target.Column - 1).Locked = False
End If

Application.EnableEvents = True
ActiveSheet.Protect

End Sub

 

[SkipVought]

Couldn't this be simplified...

    'IF Row E has data lock cell F next to it
    If Target.Column = 5 Then
        inc = 1
        If IsEmpty(Target) Then
            bLocked = False
        Else
            bLocked = True
        End If
    Else    '[b]it MUST be column 6[/b]
        inc = -1
        If IsEmpty(Target) Then
            bLocked = False
        Else
            bLocked = True
        End If
    End If
    
    Target.Offset(0, inc).Locked = bLocked

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[phoenix79]

Ok, that is MUCH cleaner. THANKS :-D