Como fazer com que a validação de dados do Excel exiba dados diferentes na lista suspensa do que é realmente validada

Question

Como posso fornecer a um usuário um menu suspenso em uma célula que exibe o conteúdo de uma coluna, mas realmente grava o valor de uma coluna diferente na célula e valida com os valores da segunda coluna?

Eu tenho um pouco de código que quase faz isso (crédito: DV0005 do site 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

A lista suspensa exibe os valores de uma coluna, por exemplo, Coluna B, mas quando selecionada, na verdade, grava o valor na mesma linha da Coluna C para a célula. No entanto, a validação de dados está realmente sendo validada na Coluna B, portanto, se eu inserir manualmente algo da Coluna C na célula e tentar mover para outra célula, a validação de dados gerará um erro.

    

Answer 1

A única maneira que vejo é remover a validação de dados e codificar sua própria caixa suspensa.

A vantagem disso é que a lista suspensa ocultará a célula real, de modo que a própria célula ainda possa ser editada como normal.

Este código (de aqui ) adicionará uma lista suspensa, e quando um item for selecionado, coloque esse valor na célula, altere outra célula com base no item selecionado e, em seguida, remova a si mesmo para que não haja nenhuma lista suspensa. Você deve poder usar este código para sua própria diversão.

Código reproduzido aqui, caso o link seja interrompido:

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

    

Answer 2

Se você não quiser usar um controle de caixa de depósito, por que não pensar nessa abordagem?

• Evento OnCellSelect captura a célula de destino
• Adicione validação suspensa na célula a ele.
• Depois de escolher a opção correta na lista suspensa na célula
O evento
• OnChange será acionado
• Capture o valor para uma variável
• Divida
• Desativar eventos para que não inicie um loop ~
• Remover validação de célula
• Reescreva o valor da célula com a divisão da variável
• Ativar eventos

A validação da célula será sempre adicionada ao evento onselect e removida no evento de alteração. Toda vez que você foca a célula, ela aparece como uma lista suspensa na validação da célula; uma vez selecionada, ela deixa de ser e você escreve o valor que deseja.