Value in one cell to determine editing options of another cell

[hassoe]

Hi!

I have the following problem:

In cell A there can be given three values AA, AB and AC from a drop-down list.
When the value AB is given, I want to block for the possibility to add data in cell B.

Anyone that has a great idea of how this can be done?


Cheers
Niels

 

[JoeMo]

Welcome to the Board. Here's a macro that goes into the worksheet you have the drop-downs on. With your workbook open to that sheet do the following:

1. right-click the sheet's name tab and select "View Code" from the menu
2. copy the code below and paste it into the white space in the VB editor that has opened
3. close the VB editor
4. save the workbook

Note if you are using Excel 2007 or 2010, you must save the workbook with a .xlsm (not .xlsx) file extension or the code will be lost when you close the workbook.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Columns(2)) Is Nothing Then
    If Target.Cells.Count > 1 Then
        MsgBox "Select only one cell at a time"
        Target.Cells(1, 1).Select
    End If
    With Target.Cells(1, 1)
        If .Offset(0, -1).Value = "AB" Then
            MsgBox "Cannot enter data in this cell while the adjacent cell in column A has a value of AB"
            .ClearContents
            .Offset(0, -1).Select
        End If
    End With
End If
End Sub

 

[hassoe]

Hi JoeMo

Thank you so much for the macro. Not being all that good with macros, I just have one small question:

If I want the macro to interact with with 2 columns (C and D), how do I add that?

 

[JoeMo]

Hi JoeMo

Thank you so much for the macro. Not being all that good with macros, I just have one small question:

If I want the macro to interact with with 2 columns (C and D), how do I add that?

This one will prevent entries in C or D if the entry in A is "AB".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Columns("C:D")) Is Nothing Then
    If Target.Cells.Count > 1 Then
        MsgBox "Select only one cell at a time"
        Target.Cells(1, 1).Select
    End If
    With Target.Cells(1, 1)
        If .Offset(0, -Target.Column + 1).Value = "AB" Then
            MsgBox "Cannot enter data in this cell while the adjacent cell in column A has a value of AB"
            .ClearContents
            .Offset(0, -Target.Column + 1).Select
        End If
    End With
End If
End Sub