Como alterar o valor na lista suspensa do Excel

Question

Eu tenho uma coluna que usa valores do conjunto predefinido (uma lista suspensa), implementada usando Validações de dados . Agora, se eu alterar algum valor na lista , o menu suspenso (quando aberto) oferecerá imediatamente esse novo valor. No entanto, a tabela não será atualizada automaticamente , o que significa que alguns dos valores da coluna serão inválidos até que eu os corrija manualmente.

Já vi soluções baseadas em macros / VBA, mas não há alguma maneira inteligente diretamente na interface do Excel?

    

Answer 1

Como mencionei nos comentários, a única maneira de fazer isso seria com o VBA.

Aqui está uma opção. Eu adicionei comentários em todo o código. Isso pressupõe que você está usando um intervalo nomeado para a lista de validação chamada "Lista" e que está na mesma planilha que as células que estão sendo validadas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim isect As Range
    Dim vOldValue As Variant, vNewValue As Variant


    Set isect = Application.Intersect(Target, ThisWorkbook.Names("List").RefersToRange)
    If Not isect Is Nothing Then
        ' Get previous value of this cell
        Application.EnableEvents = False
        With Target
            vNewValue = .Value
            Application.Undo
            vOldValue = .Value
            .Value = vNewValue
        End With

        ' For every cell with validation
        For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
            With cell
                ' If it has list validation AND the validation formula matches AND the value is the old value
                If .Validation.Type = 3 And .Validation.Formula1 = "=List" And .Value = vOldValue Then
                    ' Change the cell value
                    cell.Value = vNewValue
                End If
            End With
        Next cell
        Application.EnableEvents = True
    End If
End Sub

Você também pode fazer o download da planilha de exemplos Eu coloquei para testar isso. (Contém macros!)

    

Answer 2

but isn't there some clever way directly in the Excel UI?

Acho que conheço uma pessoa - pelo menos parece satisfazer todos os seus pedidos:

1. Você precisa definir alterar a região nomeada como uma fonte de validação de dados. Isso pode ser alcançado usando a função OFFSET . Supondo que você tenha a lista de valores para a caixa suspensa na Coluna A de Sheet1 (o nome importa apenas para a fórmula), a célula A1 tem cabeçalho, por exemplo, List of values , e os valores são colocados iniciando A2 e abaixo, você deve fazer o seguinte: ir Faixa Formulas > Name Manager , criar nova Região (vamos chamá-la Items ) e definir sua região NÃO apontando para as células, mas usando a seguinte fórmula: =OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;1) .
2. Definir regras de validação de dados : em vez de apontar para o intervalo de células para Lista, digite =Items - assim, sua região Nomeada será usada como fonte de itens de lista.
3. Como resultado do acima, você receberá uma lista verdadeiramente dinâmica que atende a todos os seus requisitos: você pode alterar / adicionar itens à coluna A livremente, e essas alterações serão refletidas imediatamente na lista suspensa na próxima vez que você usá-la. Ao mesmo tempo, os valores antigos permanecerão intactos.

Eu uso essa solução por aproximadamente 2 anos no meu trabalho. Espero que você ache útil também!

P.S. aqui está o arquivo de exemplo real: menu suspenso dinâmico