☰

如何讓 Excel 數據驗證在下拉清單中顯示與實際驗證不同的數據

tag-icon • tag-icon microsoft-excel vba
如何讓 Excel 數據驗證在下拉清單中顯示與實際驗證不同的數據

如何在儲存格中為使用者提供一個下拉式選單，顯示一列中的內容，但實際上將另一列中的值寫入儲存格並根據第二列中的值進行驗證？

我有一些程式碼幾乎可以做到這一點（來源：DV0005上下文站點）：

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

如果您不想使用保管箱控件，為什麼不考慮這種方法呢？

  • OnCellSelect 事件擷取目標單元格
  • 新增單元格內下拉驗證。
  • 從單元格內下拉清單中選擇正確的選項後
  • OnChange事件將會被觸發
  • 捕捉變數的值
  • 拆分它
  • 關閉事件，這樣就不會開始循環了~
  • 刪除單元格驗證
  • 使用變數 split 重寫儲存格值
  • 打開事件

單元格驗證將始終在onselect 事件上添加，並在更改事件上刪除。可以編寫您想要的值。

相關內容