根据vba中的相邻单元格值更改下拉列表项 [英] Change drop down list items based on adjacent cell value in vba

问题描述

我有一个Excel工作表，其中有一个包含两列和可变行数的表.这些行中的一些将被填充，而另一些将是空的.B列包含一些值，A列在每个单元格中都有一个下拉列表.

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.

我希望如果B列单元格具有某个值，则相邻的A列单元格应在下拉列表中显示3个选项-修改，添加，删除，并且当B列单元格为空时，相邻的A列单元格应仅显示其下拉列表中的一个选项-添加.可以使用VBA或excel公式完成吗?

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.

推荐答案

VBA解决方案

以下内容将达到您的期望，它将遍历B列，如果为空，则将在列A上添加一个下拉列表作为数据验证"，值为"ADD"，如果不为空，则将添加列表修改"，添加"，删除":

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

这篇关于根据vba中的相邻单元格值更改下拉列表项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！