Um de meus colegas me perguntou se é possível fazer várias seleções em uma lista suspensa no Excel.
Ao criar uma lista suspensa, você só pode fazer uma seleção. Se você selecionar outro item, o primeiro será substituído pela nova seleção.
Ele queria fazer várias seleções no mesmo menu suspenso, de forma que as seleções fossem adicionadas ao valor já presente na célula.
Algo conforme mostrado abaixo na foto:
Não há como fazer isso com os recursos integrados do Excel.
A única maneira é usar um código VBA, que é executado sempre que você faz uma seleção e adiciona o valor selecionado ao valor existente.
Assistir ao vídeo - Como selecionar vários itens de uma lista suspensa do Excel
Como fazer várias seleções em uma lista suspensa
Neste tutorial, vou mostrar como fazer várias seleções em uma lista suspensa do Excel (com repetição e sem repetição).
Este tem sido um dos tutoriais de Excel mais populares neste site. Como recebo muitas perguntas semelhantes, decidi criar uma seção de perguntas frequentes no final deste tutorial. Portanto, se você tiver alguma dúvida depois de ler isto, verifique primeiro a seção de perguntas frequentes.Existem duas partes para criar uma lista suspensa que permite várias seleções:
- Criando a lista suspensa.
- Adicionando o código VBA ao back-end.
Criação da lista suspensa no Excel
Aqui estão as etapas para criar uma lista suspensa no Excel:
- Selecione a célula ou intervalo de células onde deseja que a lista suspensa apareça (C2 neste exemplo).
- Vá para Dados -> Ferramentas de dados -> Validação de dados.
- Na caixa de diálogo Validação de dados, na guia de configurações, selecione ‘Lista’ como Critérios de validação.
- No campo Fonte, selecione as células que contêm os itens que você deseja no menu suspenso.
- Clique OK.
Agora, a célula C2 tem uma lista suspensa que mostra os nomes dos itens em A2: A6.
A partir de agora, temos uma lista suspensa onde você pode selecionar um item por vez (conforme mostrado abaixo).
Para habilitar esse menu suspenso para permitir que façamos várias seleções, precisamos adicionar o código VBA no back-end.
As próximas duas seções deste tutorial fornecerão o código VBA para permitir várias seleções na lista suspensa (com e sem repetição).
Código VBA para permitir várias seleções em uma lista suspensa (com repetição)
Abaixo está o código VBA do Excel que nos permitirá selecionar mais de um item da lista suspensa (permitindo repetições na seleção):
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' Para fazer várias seleções em uma lista suspensa no Excel Dim Oldvalue As String Dim Newvalue As String On Error GoTo Exitsub If Target.Address = "$ C $ 2" Then If Target.SpecialCells (xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else Target.Value = Oldvalue & "," & Newvalue End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Agora você precisa colocar este código em um módulo no Editor VB (conforme mostrado abaixo na seção ‘Onde colocar o código VBA’).
Depois de colocar esse código no back-end (abordado posteriormente neste tutorial), ele permitirá que você faça várias seleções no menu suspenso (conforme mostrado abaixo).
Observe que se você selecionar um item mais de uma vez, ele será inserido novamente (a repetição é permitida).
Experimente você mesmo … Baixe o arquivo de exemplo
Código VBA para permitir várias seleções em uma lista suspensa (sem repetição)
Muitas pessoas têm perguntado sobre o código para selecionar vários itens de uma lista suspensa sem repetição.
Aqui está o código que garantirá que um item só possa ser selecionado uma vez para que não haja repetições:
Private Sub Worksheet_Change (ByVal Target As Range) 'Código por Sumit Bansal de https://trumpexcel.com' Para permitir várias seleções em uma lista suspensa no Excel (sem repetição) Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = Verdadeiro em caso de erro GoTo Exitsub If Target.Address = "$ C $ 2" Then If Target.SpecialCells (xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target. Valor Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else If InStr (1, Oldvalue, Newvalue) = 0 Then Target.Value = Oldvalue & "," & Newvalue Else: Target.Value = Oldvalue End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Agora você precisa colocar este código em um módulo no Editor VB (conforme mostrado na próxima seção deste tutorial).
Este código permitirá que você selecione vários itens da lista suspensa. No entanto, você só poderá selecionar um item apenas uma vez. Se você tentar selecioná-lo novamente, nada acontecerá (como mostrado abaixo).
Experimente você mesmo … Baixe o arquivo de exemplo
Onde colocar o código VBA
Antes de começar a usar este código no Excel, você precisa colocá-lo no back-end, de modo que seja acionado sempre que houver qualquer alteração na seleção suspensa.
Siga as etapas abaixo para colocar o código VBA no backend do Excel:
- Vá para a guia Desenvolvedor e clique em Visual Basic (você também pode usar o atalho de teclado - Alt + F11). Isso abrirá o Editor do Visual Basic.
- Deve haver um painel Project Explorer à esquerda (se não estiver lá, use Control + R para torná-lo visível).
- Clique duas vezes em Nome da planilha (no painel esquerdo) onde a lista suspensa se encontra. Isso abre a janela de código para essa planilha.
- Na janela de código, copie e cole o código acima.
- Feche o Editor VB.
Agora, quando você voltar ao menu suspenso e fazer seleções, isso permitirá que você faça várias seleções (conforme mostrado abaixo):
Experimente você mesmo … Baixe o arquivo de exemplo
Observação: Como estamos usando um código VBA para fazer isso, você precisa salvar a pasta de trabalho com uma extensão .xls ou .xlsm.
Perguntas frequentes (FAQs)
Criei esta seção para responder a algumas das perguntas mais frequentes sobre este tutorial e o código VBA. Se você tiver alguma dúvida, peço que leia esta lista de perguntas primeiro.
P: No código VBA, a funcionalidade é apenas para a célula C2. Como faço para obtê-lo para outras células? Resposta: Para obter esta lista suspensa de seleção múltipla em outras células, você precisa modificar o código VBA no back-end. Suponha que você deseja obter isso para C2, C3 e C4, você precisa substituir a seguinte linha no código: If Target.Address = "$ C $ 2" Então, com esta linha: If Target.Address = "$ C $ 2" Ou Target.Address = "$ C $ 3" Ou Target.Address = "$ C $ 4" Então
P: Preciso criar vários menus suspensos em toda a coluna 'C'. Como faço para obter isso para todas as células nas colunas com funcionalidade de seleção múltipla? Resp: Para habilitar várias seleções em menus suspensos em uma coluna inteira, substitua a seguinte linha no código: If Target.Address = "$ C $ 2" Then com esta linha: If Target.Column = 3 Then Em linhas semelhantes, se você deseja esta funcionalidade nas colunas C e D, use a linha abaixo: If Target.Column = 3 ou Target.Column = 4 Then
P: Eu preciso criar vários menus suspensos em uma linha. Como posso fazer isso? Resposta: Se você precisar criar listas suspensas com várias seleções em uma linha (digamos a segunda linha), você precisará substituir a linha de código abaixo: If Target.Address = "$ C $ 2" Então, com esta linha: If Target.Row = 2 Then Da mesma forma, se você quiser que isso funcione para várias linhas (digamos, segunda e terceira linhas), use a linha de código abaixo: If Target.Row = 2 ou Target.Row = 3 Then
P: A partir de agora, as várias seleções são separadas por uma vírgula. Como posso alterar isso para separá-los com espaço (ou qualquer outro separador). Resp: Para separá-los com um separador diferente de vírgula, você precisa substituir a seguinte linha de código VBA: Target.Value = Oldvalue & "," & Newvalue por esta linha de código VBA: Target.Value = Oldvalue & "" & Newvalue Da mesma forma, se quiser alterar a vírgula com outro caractere, como |, você pode usar a seguinte linha de código: Target.Value = Oldvalue & "|" & Newvalue
P: Posso obter cada seleção em uma linha separada na mesma célula? Resposta: Sim, você pode. Para conseguir isso, você precisa substituir a linha abaixo do código VBA: Target.Value = Oldvalue & "," & Newvalue por esta linha de código: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine insere uma nova linha na mesma célula . Portanto, sempre que você fizer uma seleção no menu suspenso, ela será inserida em uma nova linha.
P: Posso fazer a funcionalidade de seleção múltipla funcionar em uma folha protegida? Resposta: Sim, você pode. Para fazer isso, você precisa fazer duas coisas: Adicionar a seguinte linha no código (logo após a instrução DIM): Me.Protect UserInterfaceOnly: = True Em segundo lugar, você precisa se certificar de que as células - que têm o menu suspenso com a funcionalidade de seleção múltipla - não estão bloqueadas quando você protege a planilha inteira. Aqui está um tutorial sobre como fazer isso: Bloquear células no Excel
