Como alterar o valor na lista suspensa do Excel

Question

Eu tenho uma coluna que usa valores de um conjunto predefinido (uma lista suspensa), implementada usandoValidações de dados. Agorase 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 na coluna serão inválidos até que eu os corrija manualmente.

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

Answer 1

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

Aqui está uma opção. Adicionei comentários ao longo do código. Isso pressupõe que você esteja usando um intervalo nomeado para a lista de validação chamada "Lista" e que esteja 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 baixar oplanilha de exemploEu montei para testar isso. (Contém macros!)

Answer 2

mas não existe uma maneira inteligente diretamente na interface do Excel?

Acho que conheço um - pelo menos parece atender a todos os seus pedidos:

1. Você precisa definiralterando dinamicamente a região nomeadacomo fonte para validação de dados. Isso pode ser conseguido usando OFFSETa função. Supondo que você tenha a lista de valores para a caixa suspensa na coluna A de Sheet1(o nome é importante apenas para a fórmula), a célula A1tem cabeçalho, por exemplo List of values, e os valores são colocados começando A2e abaixo, você deve fazer o seguinte: vá para Ribbon Formulas > Name Manager, crie uma nova região ( vamos chamá-lo de 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. DefinirData de validaderegras: em vez de apontar para o intervalo de células da Lista, digite =Items- assim, sua região nomeada será usada como origem dos itens da lista.
3. Como resultado do exposto, você receberá uma lista verdadeiramente dinâmica que atende a todos os seus requisitos: você pode alterar/adicionar itens livremente à coluna A, e essas alterações serão imediatamente refletidas no menu suspenso na próxima vez que você usá-la. Ao mesmo tempo, os valores antigos permanecerão intactos.

Eu uso essa solução há cerca de 2 anos em meu trabalho. Espero que você também ache útil!

PS aqui está o arquivo de amostra real:Menu suspenso dinâmico