☰

如何让 Excel 数据验证在下拉列表中显示与实际验证不同的数据

tag-icon • tag-icon microsoft-excel vba
如何让 Excel 数据验证在下拉列表中显示与实际验证不同的数据

如何在单元格中为用户提供一个下拉菜单，该菜单显示一列的内容，但实际上将另一列的值写入单元格并根据第二列的值进行验证？

我有一些代码几乎可以做到这一点（来源：DV0005Contextures 网站)：

Private Sub Worksheet_Change(ByVal Target As range)
On Error GoTo errHandler
If Target.Cells.Count > 1 Then GoTo exitHandler
If Target.Column = 10 Then
  If Target.Value = "" Then GoTo exitHandler
  Application.EnableEvents = False
  Target.Value = Worksheets("Measures").range("B1") _
    .Offset(Application.WorksheetFunction _
    .Match(Target.Value, Worksheets("Measures").range("Measures"), 0) - 1, 1)
End If

下拉列表显示一列的值，例如 B 列，但选中后实际上会将 C 列同一行的值写入单元格。但是，数据验证实际上是针对 B 列进行验证，因此如果我手动在单元格中输入 C 列中的某些内容并尝试移动到另一个单元格，数据验证会抛出错误。

答案1

我看到的唯一方法是删除数据验证，并编写自己的下拉框。

这样做的好处是下拉菜单会隐藏实际的单元格，因此单元格本身仍可正常编辑。

此代码（来自这里) 将添加一个下拉列表，当选择某项时，将该值放入单元格中，根据所选项更改另一个单元格，然后删除自身，这样就不存在下拉列表了。您应该能够使用此代码来获得乐趣。

此处复制代码，以防链接中断：

Option Explicit

Sub Test()
    AddDropDown Range("D4")
End Sub

Sub AddDropDown(Target As Range)
    Dim ddBox As DropDown
    Dim vaProducts As Variant
    Dim i As Integer

    vaProducts = Array("Water", "Oil", "Chemicals", "Gas")
    Set ddBox = Sheet1.DropDowns.Add(Target.Left, Target.Top, Target.Width, Target.Height)
    With ddBox
        .OnAction = "EnterProductInfo" ' name corrected
        For i = LBound(vaProducts) To UBound(vaProducts)
            .AddItem vaProducts(i)
        Next i
    End With
End Sub

Private Sub EnterProductInfo()
    Dim vaPrices As Variant

    vaPrices = Array(15, 12.5, 20, 18)
    With Sheet1.DropDowns(Application.Caller)
        .TopLeftCell.Value = .List(.ListIndex)
        .TopLeftCell.Offset(0, 2).Value = vaPrices(.ListIndex - Array(0, 1)(1))
        .Delete
    End With
End Sub

答案2

如果您不想使用 Dropbox 控件为什么不考虑这种方法呢？

  • OnCellSelect 事件捕获目标单元格
  • 向其中添加单元格下拉验证。
  • 从单元格下拉列表中选择正确的选项后
  • OnChange 事件将被触发
  • 将值捕获到变量中
  • 分割
  • 关闭事件，这样它就不会开始循环~
  • 删除单元格验证
  • 使用变量 split 重写单元格值
  • 打开事件

单元格验证将始终在 onselect 事件中添加，并在 change 事件中删除。每次您聚焦单元格时，它都会在单元格验证中显示为下拉列表，一旦被选中，它就不再是它，您可以写入您想要的值。

相关内容