☰

如何更改 Excel 下拉列表中的值

tag-icon • tag-icon microsoft-excel
如何更改 Excel 下拉列表中的值

我有一列使用预定义集（下拉列表）中的值，使用以下方式实现数据验证。 现在如果我改变列表中的某些值，下拉菜单（打开时）将立即提供此新值。但是，该表不会自动更新，这意味着该列中的某些值将无效，直到我手动修复它们。

我已经看到了基于宏/VBA 的解决方案，但是在 Excel UI 中难道没有直接的一些巧妙的方法吗？

答案1

正如我在评论中提到的，唯一的方法是使用 VBA。

这是一个选项。我在整个代码中添加了注释。这假设您正在使用名为“List”的验证列表的命名范围，并且它与正在验证的单元格位于同一工作表上。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim isect As Range
    Dim vOldValue As Variant, vNewValue As Variant


    Set isect = Application.Intersect(Target, ThisWorkbook.Names("List").RefersToRange)
    If Not isect Is Nothing Then
        ' Get previous value of this cell
        Application.EnableEvents = False
        With Target
            vNewValue = .Value
            Application.Undo
            vOldValue = .Value
            .Value = vNewValue
        End With

        ' For every cell with validation
        For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
            With cell
                ' If it has list validation AND the validation formula matches AND the value is the old value
                If .Validation.Type = 3 And .Validation.Formula1 = "=List" And .Value = vOldValue Then
                    ' Change the cell value
                    cell.Value = vNewValue
                End If
            End With
        Next cell
        Application.EnableEvents = True
    End If
End Sub

您还可以下载示例电子表格我把它们放在一起测试了一下。（包含宏！）

答案2

但是在 Excel UI 中难道没有直接的一些巧妙的方法吗？

我想我知道一个 - 至少它似乎满足你的所有要求：

  1. 您需要设置动态改变命名区域作为数据验证的来源。这可以使用OFFSET函数实现。假设您在 A 列中有一个下拉框的值列表Sheet1（名称仅对公式有用），单元格A1有标题（例如） List of values，并且值放在开头A2和下方，您应该执行以下操作：转到功能区Formulas > Name Manager，创建新区域（我们称之为Items）并将其区域设置为不指向单元格，而是使用以下公式：=OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;1)。
  2. 放数据验证规则：不要指向列表的单元格范围，而是输入=Items- 这样您的命名区域将用作列表项源。
  3. 以上操作的结果是，您将获得真正动态的列表，满足您的所有要求：您可以自由更改/添加 A 列中的项目，这些更改将在您下次使用时立即反映在下拉列表中。同时，旧值将保持不变。

我在工作中使用这个解决方案大约 2 年了。希望您也会发现它很有用！

PS 这里是实际的示例文件：动态下拉菜单

相关内容