Change drop down list items based on adjacent cell value in vba

Question

I have an excel sheet where I have a table with two columns and variable number of rows. Some of these rows will be filled and some empty. Column B contains some values and Column A has a drop down list in each cell.

I want that if column B cell has some value then the adjacent column A cell should show 3 options in the drop down list - MODIFY, ADD, DELETE and when column B cell is empty then the adjacent column A cell should show only one option in its drop down list - ADD. Can it be done using VBA or excel formulas?

Please don't confuse it with changing cell values based on drop down selection. It's the opposite.

Answer 1

Solution without VBA

You can do this with the Data Validation tool of Excel:

First you need to add all the drop down options (ADD, MODIFY, DELETE) into a range. That can be in the same worksheetor in any other worksheet. For my example I put them into range F1:F3 of the same worksheet.

Then select the whole column A and add a data validation from Ribbon Menu › Data › Data Tools › Data Validation:

On the Settings tab, under Allow, select List and use the following formula at Source:

=IF(B1<>"",$F$1:$F$3,$F$1)

Note that the first range $F$1:$F$3 points to ADD, MODIFY, DELETE and the second range $F$1 points to ADD only!

Answer 2

VBA Solution

The following will achieve what you expect, it will loop through your column B and if empty it will add a drop-down as Data Validation on Column A with the value "ADD", if not empty it will add the list "MODIFY", "ADD", "DELETE":

Sub foo()
Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
'declare and set the worksheet you are using, amend as required
LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
'get the last row with data on Column B

For i = 2 To LastRow 'loop from Row 2 to Last
    If ws.Cells(i, "B").Value <> "" Then
        With ws.Cells(i, "A").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="MODIFY,ADD,DELETE"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
    Else
        With ws.Cells(i, "A").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="ADD"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
    End If
Next i
End Sub

Answer 3

Name your list like below ,

After that create a data validation with this formula

=INDIRECT(IF($C2<>"","All","One"))

You'll have the drop down as per your requirement