☰

如何更改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和下方，您應該執行以下操作：前往 Ribbon Formulas > Name Manager，建立新區域（讓我們稱之為Items) 並將其區域設為不指向單元格，而是使用以下公式：=OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;1)。
  2. 放數據驗證規則：不要指向清單的儲存格範圍，而是鍵入=Items- 因此您的命名區域將用作清單項目來源。
  3. 由於上述原因，您將收到滿足您所有要求的真正動態清單：您可以自由變更/新增項目到 A 列，這些變更將在您下次使用時立即反映在下拉清單中。同時，舊的價值觀將保持不變。

我在工作中使用這個解決方案大約有 2 年。希望您也會發現它很有用！

PS這是實際的範例文件：動態下拉選單

相關內容