Start Free Trial

asked on

Excel vba nested if statements

This code is working that colum AK is locked if AJ has a value and AJ is locked if AK has a value
AL is unlocked if either has “Other comment required”

Want to be able to lock AL if both AK and AJ are null

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub

If Target.Column = 36 And Target.Row > 1 Then

ActiveSheet.Unprotect Password:=PW
If Target = "Other (comment required)" Then
Cells(Target.Row, "AK").Locked = True
Cells(Target.Row, "AL").Locked = False

ElseIf Target <> "" Then
Cells(Target.Row, "AK").Locked = True
Cells(Target.Row, "AL").Locked = True

End If

ActiveSheet.Protect Password:=PW

ElseIf Target.Column = 37 And Target.Row > 1 Then
ActiveSheet.Unprotect Password:=PW

If Target = "Other (comment required)" Then
Cells(Target.Row, "AJ").Locked = True
Cells(Target.Row, "AL").Locked = False

ElseIf Target <> "" Then
Cells(Target.Row, "AJ").Locked = True
Cells(Target.Row, "AL").Locked = True

ActiveSheet.Protect Password:=PW

End If

I think this is tidier

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Row = 1 Then Exit Sub

    Me.Unprotect
  
    Select Case Target.Column
    Case 36
        If Target = "Other (comment required)" Then
            Cells(Target.Row, "AK").Locked = True
            Cells(Target.Row, "AL").Locked = False

        ElseIf Target <> "" Then
            Cells(Target.Row, "AK").Locked = True
            Cells(Target.Row, "AL").Locked = True

        End If

    Case 37
        If Target = "Other (comment required)" Then
            Cells(Target.Row, "AJ").Locked = True
            Cells(Target.Row, "AL").Locked = False

        ElseIf Target <> "" Then
            Cells(Target.Row, "AJ").Locked = True
            Cells(Target.Row, "AL").Locked = True
        End If

    End Select
    Me.Protect
End Sub

Open in new window

What exactly do you mean by the other criteria

Want to be able to lock AL if both AK and AJ are null

ASKER

I want the cell in column AL if the cells in the same row in column AK and AJ are null

Maybe, untested but let me know if it needs any changes

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Row = 1 Then Exit Sub

    Me.Unprotect
    'Want to be able to lock AL if both AK and AJ are null
    Select Case Target.Column
    Case 36
        If Target = "Other (comment required)" Then
            Cells(Target.Row, "AK").Locked = True
            Cells(Target.Row, "AL").Locked = False

        ElseIf Target <> "" Then
            Cells(Target.Row, "AK").Locked = True
            Cells(Target.Row, "AL").Locked = True

        End If

    Case 37
        If Target = "Other (comment required)" Then
            Cells(Target.Row, "AJ").Locked = True
            Cells(Target.Row, "AL").Locked = False

        ElseIf Target <> "" Then
            Cells(Target.Row, "AJ").Locked = True
            Cells(Target.Row, "AL").Locked = True
        End If

    End Select
    
    If Cells(Target.Row, 36) And Cells(Target.Row, 37) = vbNullString Then Cells(Target.Row, "AL").Locked = True
    
    Me.Protect
End Sub

Open in new window

ASKER

Now AL is not locking at all.

What I need is
cell in row for Column AK is locked if cell in column AJ has a value and AJ is locked if AK has a value
AL is unlocked if either has “Other comment required”

Want to be able to lock AL if both AK and AJ are null

can you attach a small example workbook

ASKER

This is the code I am using and need to add that if both target columns shown below are null that AL is lock. Not able to load worksheet.

Option Explicit

Const PW As String = "123"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub

If Target.Column = 36 And Target.Row > 1 Then

ActiveSheet.Unprotect Password:=PW
If Target = "Other (comment required)" Then
Cells(Target.Row, "AK").Locked = True
Cells(Target.Row, "AL").Locked = False

ElseIf Target <> "" Then
Cells(Target.Row, "AK").Locked = True
Cells(Target.Row, "AL").Locked = True

End If

ActiveSheet.Protect Password:=PW

ElseIf Target.Column = 37 And Target.Row > 1 Then
ActiveSheet.Unprotect Password:=PW

If Target = "Other (comment required)" Then
Cells(Target.Row, "AJ").Locked = True
Cells(Target.Row, "AL").Locked = False

ElseIf Target <> "" Then
Cells(Target.Row, "AJ").Locked = True
Cells(Target.Row, "AL").Locked = True

ActiveSheet.Protect Password:=PW

End If

ASKER

What I need is
cell in row for Column AK is locked if cell in column AJ has a value and AJ is locked if AK has a value
AL is unlocked if either has “Other comment required”

Want to be able to lock AL if both AK and AJ are null

right now I have the above logic and need to add this

I'll have to get back to this later for you.

ASKER

ok - thank you for taking the time and letting me know

Would this work:

Option Explicit

Const PW As String = "123"
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Or Target.Row = 1 Then Exit Sub
    Me.Unprotect

    Select Case Target.Column
        Case 36, 37
            If Target <> "" Then
                Cells(Target.Row, Iif(Target.Column = 36, "AK", "AJ")).Locked = True
                If Target = "Other (comment required)" Then Cells(Target.Row, "AL").Locked = False
            End If
    End Select
    
    If Cells(Target.Row, 36) = vbNullString And Cells(Target.Row, 37) = vbNullString Then Cells(Target.Row, "AL").Locked = True
    
    Me.Protect
End Sub

Open in new window

ASKER

Kevin that is doing exaclty what I am looking for. Running into once glitch - When i put a value in Column 36 or 37 and then try and remove it - it tells me cell is locked

Maybe it is sneaking by the <> "" if the value in the other column is null as you checked with vbNullString. But guess you can put in a failsafe that a field with value is unlocked.

            If Target <> "" Then
                Cells(Target.Row, Target.Column).Locked = False
                Cells(Target.Row, Iif(Target.Column = 36, "AK", "AJ")).Locked = True
                If Target = "Other (comment required)" Then Cells(Target.Row, "AL").Locked = False
            End If

Open in new window

And probably a good idea to say Target <> vbNullString.

ASKER

I can now remove a value but if i go to put another value in the other cell it is locked

ASKER

This is the code I have now. Work great except when I go and change either column 36 or 37 to null I am not able to change the other. So if I had a value in 36 and deleted it and then try to put a value in 37 it will not let me

Option Explicit

Const PW As String = "123"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Row = 1 Then Exit Sub

ActiveSheet.Unprotect Password:=PW

Select Case Target.Column
Case 36, 37
If Target <> vbNullString Then
Cells(Target.Row, IIf(Target.Column = 36, "AK", "AL")).Locked = True
Cells(Target.Row, IIf(Target.Column = 37, "AJ", "AL")).Locked = True
If Target = "Other (comment required)" Then Cells(Target.Row, "AL").Locked = False
End If
End Select

If Cells(Target.Row, 36) = vbNullString And Cells(Target.Row, 37) = vbNullString Then Cells(Target.Row, "AL").Locked = True

ActiveSheet.Protect Password:=PW
End Sub

ASKER

Option Explicit

Const PW As String = "123"
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Or Target.Row = 1 Then Exit Sub
 
 ActiveSheet.Unprotect Password:=PW

  Select Case Target.Column
        Case 36, 37
            If Target <> vbNullString Then
                Cells(Target.Row, IIf(Target.Column = 36, "AK", "AL")).Locked = True
                Cells(Target.Row, IIf(Target.Column = 37, "AJ", "AL")).Locked = True
                If Target = "Other (comment required)" Then Cells(Target.Row, "AL").Locked = False
            End If
    End Select
    
    If Cells(Target.Row, 36) = vbNullString And Cells(Target.Row, 37) = vbNullString Then Cells(Target.Row, "AL").Locked = True

    
    ActiveSheet.Protect Password:=PW
End Sub

Open in new window

ASKER

What do I have wrong here?

This statement Iif(Target.Column = 36, "AK", "AJ") was meant to lock the opposite column. I figured on column 36 you want to lock column AK and on 37 lock AJ. Maybe I put those backwards?

If Target <> vbNullString Then
Cells(Target.Row, Target.Column).Locked = False
Cells(Target.Row, Iif(Target.Column = 36, "AK", "AJ")).Locked = True
If Target = "Other (comment required)" Then Cells(Target.Row, "AL").Locked = False
Else
'Value is null here, so unlock opposite one
Cells(Target.Row, Iif(Target.Column = 36, "AK", "AJ")).Locked = False
End If

Again, you will need to double check that I have the right column AK or AJ based on the target column. I may be misunderstanding what you have currently.

ASKER

No you have that correct on what I want to do . My code is wrong then -learning so thank you for explaining. What happens now is if put a value in column 36 then column AK is locked (perfect) but if i remove the value from 36 then AK is still locked.

ASKER

Let me try what you just posted

ASKER

Perfect. Now this part of the vba does not seem to be working but I can live with that.

If Cells(Target.Row, 36) = vbNullString And Cells(Target.Row, 37) = vbNullString Then Cells(Target.Row, "AL").Locked = True

ASKER

Unless you can think why this line wouldn't work?

I am missing why it is not working too. It probably is something simple but think you can put that in this logic.

Else
'Value is null here, so unlock opposite one
Cells(Target.Row, Iif(Target.Column = 36, "AK", "AJ")).Locked = False
If Cells(Target.Row, Iif(Target.Column = 36, "AK", "AJ")).Value = vbNullString Then Cells(Target.Row, "AL").Locked = True
End If

Ha. And as you can see while I was typing this I realized what might be wrong is we are looking at cells when we probably want to look at its value. Forgot that since Target works.

ASKER

Now AL is always unlocked

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thank you for all your help