Как сделать так, чтобы при проверке данных Excel в раскрывающемся списке отображались данные, отличные от фактически проверенных

Question

Как я могу предоставить пользователю выпадающее меню в ячейке, которое отображает содержимое из одного столбца, но на самом деле записывает значение из другого столбца в ячейку и сравнивает значения из этого второго столбца?

У меня есть немного кода, который очень близко делает это (кредит: DV0005 с сайта Contextures):

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 в ячейку и попытаюсь перейти в другую ячейку, проверка данных приведет к ошибке.

Answer 1

Если вы не хотите использовать элемент управления Dropbox, почему бы не подумать об этом подходе?

• Событие OnCellSelect захватывает целевую ячейку
• Добавьте в ячейку проверку выпадающего списка.
• После выбора правильной опции из выпадающего списка
• Событие OnChange будет запущено
• Захватить значение в переменную
• Раздели это
• Выключите события, чтобы не запускать цикл ~
• Удалить проверку ячейки
• Перепишите значение ячейки с помощью переменной split
• Включить события

Проверка ячейки всегда будет добавляться в событие onselect и удаляться в событии change. Каждый раз, когда вы фокусируете ячейку, она появляется как раскрывающийся список проверки ячейки, после выбора она перестает быть ею, и вы записываете значение, которое хотите, чтобы оно было.

Answer 2

Единственный способ, который я вижу, - это удалить проверку данных и написать свой собственный выпадающий список.

Преимущество этого состоит в том, что раскрывающийся список скрывает фактическую ячейку, поэтому сама ячейка все еще может быть отредактирована как обычно.

Этот код (отсюда) добавит выпадающий список, и когда элемент будет выбран, поместите это значение в ячейку, измените другую ячейку на основе выбранного элемента, а затем удалите себя, чтобы не было раскрывающегося списка. Вы должны быть в состоянии использовать этот код для собственного удовольствия.

Код воспроизводится здесь, на случай, если ссылка не работает:

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