Value in one cell to determine editing options of another cell

hassoe

New Member
Joined
Oct 25, 2011
Messages
13
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Oct 25, 2011
Messages
13
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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".
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,043
Messages
5,599,479
Members
414,312
Latest member
mikefire911

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top