How can I provide a user with a drop-down menu in a cell that displays the contents from one column but actually writes the value from a different column to the cell and validates against the values from that second column?

I have a bit of code that very nearly does this (credit: DV0005 from the Contextures site):

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

The drop-down displays the values from one column, for example Column B, but when selected actually writes the value on the same row from Column C to the cell. However, data validation is actually validating against Column B, so if I manually enter something from Column C in the cell and try to move to another cell, data validation throws an error.

  • this interests me, but I can not quite follow your description. Worksheet_Change is an event called on I think nearly any change on your workbook - not just, if your drop-down menu changes. How exactly do you provide your drop-down menu, and how do you validate the data? – Jook Sep 25 '12 at 7:28
  • Worksheet_Change is called for any change event but I just throw conditionals in to determine if something changed that I care about and ignore the rest, hence the "If Target.Column" conditional. The drop-down was created using Data Validation against a named range. – Memitim Nov 16 '12 at 15:43

The only way I see would be to remove the data validation, and code your own drop down box.

The advantage of this is that the drop down will hide the actual cell, so the cell itself can still be edited as normal.

This code (from here) will add a drop down, and when an item is selected, put that value in the cell, change another cell based on the item selected, and then remove itself so no drop down exists. You should be able to use this code for your own fun.

Code reproduced here, just in case the link breaks:

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))
    End With
End Sub
  • Yeah, I have to agree that the programmatic approach is the only one to completely get it right. Had it so close, too. Can get the drop down list to display what I wanted and save the value to the cell that I wanted, but data validation applied to the displayed list. So the user would drop down the list, select "1122 - Sweet Computer", and "1122" would be entered into the cell. However, the cell was being validated against the list displayed (i.e., "1122 - Sweet Computer") so if the user entered "1122" manually, the cell would fail validation. – Memitim Nov 16 '12 at 15:46

If you don't want to use a dropbox control why not to think about this aproach?

  • OnCellSelect event capture the Target cell
  • Add in-cell drop down validation to it.
  • After picking the correct option from the in-cell dropdown
  • OnChange event will be triggered
  • Capture the value to a variable
  • Split it
  • Turn Off events so it won't start a loop~
  • Remove cell validation
  • Rewrite the cell value with the variable split
  • Turn On events

Cell validation will always be added on the onselect event and removed on the change event.. Everytime you focus the cell it appears as a dropdown in cell validation, once is selected it stops being it and you write the value you want it to be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.