Conditional Locking of Cell if an adjacent Cell is Blank

[Bwanna]

Hi,

Started a new thread so as to no hijack the previous one... hope that's correct protocol.

What I want to accomplish: B1 has a dropdown menu and if no selections have been made then any attempt to select A1 will result in B1 being selected (highlighted) as a reminder to the user that a dropdown selection must first be done.

In the thread (https://www.mrexcel.com/forum/excel-questions/477560-conditional-locked-cells.html)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Intersect(Target, Range("K2:K22")) Is Nothing) And Target.Cells.Count = 1 Then
        ActiveSheet.Unprotect
        If Target.Value = "Disagree" Then
            Target.Offset(0, 1).Locked = False
        Else
            Target.Offset(0, 1).Locked = True
        End If
        ActiveSheet.Protect
    End If
End Sub

I like the way that works with the locking of the sheet (which is something I'll be doing anyway); however, I wasn't able to figure out how to edit the code to cause the same action as THIS code does:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If [B1] = ISBLANK Then
            Application.EnableEvents = False
            [B1].Select
            Application.EnableEvents = True
        End If
    End If

Thanks,
Bwanna

 

[Bwanna]

I forgot to add this is to apply to a range of cells in two columns:
A1 is locked by B1 being blank
A2 is locked by B2 being blank
A3 is locked by B3 being blank
etc.

Thanks!

 

[mumps]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If Range("B1") = "" Then
        MsgBox ("You must make a selection in the drop down list in B1.")
        Range("B1").Select
    End If
End Sub

The cell in column A is not locked but it doesn't have to be because you won't have access to it unless column B is populated.

 

[mumps]

To answer you last request:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    If Range("B" & Target.Row) = "" Then
        MsgBox ("You must make a selection in the drop down list in cell " & Target.Offset(0, 1).Address(0, 0))
        Range("B" & Target.Row).Select
    End If
End Sub

The cell in column A is not locked but it doesn't have to be because you won't have access to it unless column B is populated.

 

[Bwanna]

To answer you last request:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    If Range("B" & Target.Row) = "" Then
        MsgBox ("You must make a selection in the drop down list in cell " & Target.Offset(0, 1).Address(0, 0))
        Range("B" & Target.Row).Select
    End If
End Sub

The cell in column A is not locked but it doesn't have to be because you won't have access to it unless column B is populated.

Understand and correct... so maybe is more accurate to says
A1 is BLOCKED by B1 being blank
etc

I prefer to use a LOCKED approach coding since the sheet will be locked anyway.

Two things I couldn't figure out how to do:
1. Instead of "If Target.Value = "Disagree" Then" How can the "Disagree" be changed to check if the cell is BLANK?
2. Make the Cell selection action occur as described above.

 

[mumps]

I'm not sure what you mean. Can you explain in detail using a few examples and referring to specific cells? Do you want to use the 'locked cell' approach or the 'blocked cell' approach? If you want to lock the cells, then I would need to know if you are using a password to protect the sheet.

 

[Bwanna]

I'm not sure what you mean. Can you explain in detail using a few examples and referring to specific cells? Do you want to use the 'locked cell' approach or the 'blocked cell' approach? If you want to lock the cells, then I would need to know if you are using a password to protect the sheet.

LOCKED approach is preferred. No password.

Example:
Conditions:
1. Dropdown List configured for Column B1:B10 (List offers 6 choices)
2. All cells are BLANK

Operation:
- Attempted to Select A1, as a result, B1 is automatically selected
- Then: fill B1 with a selection from the DropDown
- Then: able to select A1 and enter information

You'll notice in the OP 2nd code example does the Selection by "[B1].Select"

Does this help?

 

[Bwanna]

I think I did it! I realized I used the Blocked Cell approach, but if I unlock the affected cells as appropriate, the Blocked coding will work just the same.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub                                 'only 1 cell can be selected
    If Not Intersect(Target, Range("A1:A5")) Is Nothing Then            'action only occurs if these cells are selected
        If Target.Offset(0, 1) = "" Then                               'IF Cell to the right is empty
            Application.EnableEvents = False
            Target.Offset(0, 1).Select
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[mumps]

Glad it worked out.