☰

Excel のデータ検証で、実際に検証されたものとは異なるデータをドロップダウンに表示する方法

tag-icon • tag-icon microsoft-excel vba
Excel のデータ検証で、実際に検証されたものとは異なるデータをドロップダウンに表示する方法

ある列の内容を表示し、実際には別の列の値をセルに書き込み、その 2 番目の列の値に対して検証するセル内のドロップダウン メニューをユーザーに提供するにはどうすればよいですか?

私はこれとほぼ同じことを実現するコードを持っています（出典：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 などの 1 つの列の値が表示されますが、選択すると、実際には同じ行の列 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 イベントで削除されます。セルにフォーカスするたびに、セル検証のドロップダウンとして表示されます。選択するとドロップダウンではなくなり、必要な値を入力します。

関連情報