Selecionar vários itens na lista suspensa do Excel – guia completo
As listas suspensas no Excel são uma ferramenta fantástica para garantir consistência de dados e facilitar a entrada. No entanto, por padrão, elas restringem você a selecionar apenas um item. Mas e se você precisar selecionar vários itens da mesma lista suspensa? Este guia abrangente explorará métodos para habilitar múltiplas seleções em listas suspensas do Excel, gerenciar duplicatas, definir separadores personalizados e estabelecer o escopo dessas listas.
- Permitindo itens duplicados
- Removendo quaisquer itens existentes
- Definindo um separador personalizado
- Definindo um intervalo específico
- Executando em uma planilha protegida
Habilitando Múltiplas Seleções na Lista Suspensa
Esta seção fornece dois métodos para ajudá-lo a habilitar múltiplas seleções na lista suspensa no Excel.
Usando Código VBA
Para permitir múltiplas seleções em uma lista suspensa, você pode usar "Visual Basic for Applications" (VBA) no Excel. O script pode modificar o comportamento de uma lista suspensa para transformá-la em uma lista de múltipla escolha. Por favor, faça o seguinte.
Passo 1: Abra o editor de Código da Planilha
- Abra a planilha que contém a lista suspensa para a qual você deseja habilitar a seleção múltipla.
- Clique com o botão direito na aba da planilha e selecione "Ver Código" no menu de contexto.
Passo 2: Use o código VBA
Agora copie o seguinte código VBA e cole-o na janela de Código da Planilha que foi aberta.
Código VBA: Habilite múltiplas seleções na lista suspensa do Excel.
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Set TargetRange = Me.UsedRange ' Users can change target range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
If Not (xValue1 = xValue2 Or _
InStr(1, xValue1, delimiter & xValue2) > 0 Or _
InStr(1, xValue1, xValue2 & delimiter) > 0) Then
Target.Value = xValue1 & delimiter & xValue2
Else
Target.Value = xValue1
End If
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
Resultado
Quando você retornar à planilha, a lista suspensa permitirá que você escolha várias opções, veja a demonstração abaixo:
O código VBA acima:
- Aplica-se a todas as listas suspensas de validação de dados na planilha atual, tanto as existentes quanto as criadas futuramente.
- Impede que você selecione o mesmo item mais de uma vez em cada lista suspensa.
- Usa vírgula como separador para os itens selecionados. Para usar outros delimitadores, por favor, veja esta seção para alterar o separador.
Usando Kutools para Excel em poucos cliques
Se você não está confortável com VBA, uma alternativa mais fácil é o recurso "Lista Suspensa de Múltipla Seleção" do "Kutools para Excel". Esta ferramenta amigável simplifica a habilitação de múltiplas seleções em listas suspensas, permitindo que você personalize o separador e gerencie duplicatas facilmente para atender às suas diferentes necessidades.
Após instalar Kutools para Excel, vá até a aba "Kutools", selecione "Lista suspensa" > "Lista Suspensa de Múltipla Seleção". Em seguida, você precisa configurar conforme segue.
- Especifique o intervalo que contém a lista suspensa da qual você precisa selecionar vários itens.
- Especifique o separador para os itens selecionados na célula da lista suspensa.
- Clique em "OK" para concluir as configurações.
Resultado
Agora, quando você clicar em uma célula com uma lista suspensa no intervalo especificado, uma caixa de listagem aparecerá ao lado dela. Basta clicar no botão "+" ao lado dos itens para adicioná-los à célula da lista suspensa, e clique no botão "-" para remover qualquer item que você não deseja mais. Veja a demonstração abaixo:
- Marque a opção "Inserir quebra de linha após o separador" se você deseja exibir os itens selecionados verticalmente dentro da célula. Se preferir uma listagem horizontal, deixe essa opção desmarcada.
- Marque a opção "Ativar função de pesquisa" se você deseja adicionar uma barra de pesquisa à sua lista suspensa.
- Para aplicar este recurso, por favor, baixe e instale Kutools para Excel primeiro.
Mais operações para lista suspensa de múltipla seleção
Esta seção coleta os diferentes cenários que podem ser necessários ao habilitar múltiplas seleções na lista suspensa de Validação de Dados.
Permitindo itens duplicados na lista suspensa
Duplicatas podem ser um problema quando múltiplas seleções são permitidas em uma lista suspensa. O código VBA acima não permite itens duplicados na lista suspensa. Se você precisar manter itens duplicados, tente o código VBA nesta seção.
Código VBA: Permitir duplicatas na lista suspensa de validação de dados
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Set TargetRange = Me.UsedRange ' Users can change target range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
Target.Value = xValue1 & delimiter & xValue2
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
Resultado
Agora você pode selecionar vários itens das listas suspensas na planilha atual. Para repetir um item em uma célula da lista suspensa, continue selecionando esse item da lista. Veja a captura de tela:
Removendo quaisquer itens existentes da lista suspensa
Depois de selecionar vários itens de uma lista suspensa, às vezes você pode precisar remover um item existente da célula da lista suspensa. Esta seção fornece outro trecho de código VBA para ajudá-lo a realizar essa tarefa.
Código VBA: Remover quaisquer itens existentes da célula da lista suspensa
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRngDV As Range
Dim TargetRange As Range
Dim oldValue As String
Dim newValue As String
Dim delimiter As String
Dim allValues As Variant
Dim valueExists As Boolean
Dim i As Long
Dim cleanedValue As String
Set TargetRange = Me.UsedRange ' Set your specific range here
delimiter = ", " ' Set your desired delimiter here
If Target.CountLarge > 1 Then Exit Sub
' Check if the change is within the specific range
If Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRngDV = Target.SpecialCells(xlCellTypeAllValidation)
If xRngDV Is Nothing Or Target.Value = "" Then
' Skip if there's no data validation or if the cell is cleared
Application.EnableEvents = True
Exit Sub
End If
On Error GoTo 0
If Not Intersect(Target, xRngDV) Is Nothing Then
Application.EnableEvents = False
newValue = Target.Value
Application.Undo
oldValue = Target.Value
Target.Value = newValue
' Split the old value by delimiter and check if new value already exists
allValues = Split(oldValue, delimiter)
valueExists = False
For i = LBound(allValues) To UBound(allValues)
If Trim(allValues(i)) = newValue Then
valueExists = True
Exit For
End If
Next i
' Add or remove value based on its existence
If valueExists Then
' Remove the value
cleanedValue = ""
For i = LBound(allValues) To UBound(allValues)
If Trim(allValues(i)) <> newValue Then
If cleanedValue <> "" Then cleanedValue = cleanedValue & delimiter
cleanedValue = cleanedValue & Trim(allValues(i))
End If
Next i
Target.Value = cleanedValue
Else
' Add the value
If oldValue <> "" Then
Target.Value = oldValue & delimiter & newValue
Else
Target.Value = newValue
End If
End If
Application.EnableEvents = True
End If
End Sub
Resultado
Este código VBA permite que você selecione vários itens de uma lista suspensa e remova facilmente qualquer item que já tenha escolhido. Após selecionar vários itens, se você quiser remover um específico, basta selecioná-lo novamente na lista.
Definindo um separador personalizado
O delimitador é definido como vírgula nos códigos VBA acima. Você pode modificar essa variável para qualquer caractere preferido para usar como separador para as seleções da lista suspensa. Aqui está como fazer isso:
Como você pode ver, os códigos VBA acima todos têm a seguinte linha:
delimiter = ", "
Você só precisa alterar a vírgula para qualquer separador que precise. Por exemplo, se você deseja separar os itens por ponto e vírgula, altere a linha para:
delimiter = "; "
delimiter = vbNewLine
Definindo um intervalo específico
Os códigos VBA acima se aplicam a todas as listas suspensas na planilha atual. Se você deseja que os códigos VBA se apliquem apenas a um determinado intervalo de listas suspensas, você pode especificar o intervalo no código VBA acima da seguinte forma.
Como você pode ver, os códigos VBA acima todos têm a seguinte linha:
Set TargetRange = Me.UsedRange
Você só precisa alterar a linha para:
Set TargetRange = Me.Range("C2:C10")
Executando em uma planilha protegida
Imagine que você protegeu uma planilha com a senha "123" e definiu as células da lista suspensa como "Desbloqueadas" antes de ativar a proteção, garantindo assim que a função de multi-seleção permaneça ativa após a proteção. No entanto, os códigos VBA mencionados acima não funcionam nesse caso, e esta seção descreve outro script VBA especificamente projetado para lidar com a funcionalidade de multi-seleção em uma planilha protegida.
Código VBA: Habilite múltiplas seleções na lista suspensa sem duplicatas
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Dim isProtected As Boolean
Dim pswd As Variant
Set TargetRange = Me.UsedRange ' Set your specific range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
' Check if sheet is protected
isProtected = Me.ProtectContents
If isProtected Then
' If protected, temporarily unprotect. Adjust or remove the password as needed.
pswd = "yourPassword" ' Change or remove this as needed
Me.Unprotect Password:=pswd
End If
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then
If isProtected Then Me.Protect Password:=pswd
Exit Sub
End If
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
If Not (xValue1 = xValue2 Or _
InStr(1, xValue1, delimiter & xValue2) > 0 Or _
InStr(1, xValue1, xValue2 & delimiter) > 0) Then
Target.Value = xValue1 & delimiter & xValue2
Else
Target.Value = xValue1
End If
End If
Application.EnableEvents = True
On Error GoTo 0
' Re-protect the sheet if it was protected
If isProtected Then
Me.Protect Password:=pswd
End If
End Sub
Ao habilitar múltiplas seleções em listas suspensas do Excel, você pode melhorar muito a funcionalidade e flexibilidade de suas planilhas. Quer você esteja confortável com codificação VBA ou prefira uma solução mais direta como o Kutools, agora você tem a capacidade de transformar suas listas suspensas padrão em ferramentas dinâmicas de múltipla seleção. Com essas habilidades, você agora está equipado para criar documentos do Excel mais dinâmicos e fáceis de usar. Para aqueles ansiosos para explorar mais as capacidades do Excel, nosso site possui uma riqueza de tutoriais. Descubra mais dicas e truques do Excel aqui.
Artigos Relacionados
Autocompletar ao digitar na lista suspensa do Excel
Se você tiver uma lista suspensa de validação de dados com muitos valores, será necessário rolar para baixo na lista apenas para encontrar o valor adequado, ou digitar a palavra inteira diretamente na caixa de listagem. Se houver um método que permita autocompletar ao digitar a primeira letra na lista suspensa, tudo ficará mais fácil. Este tutorial fornece o método para resolver o problema.
Criar lista suspensa de outra pasta de trabalho no Excel
É bastante fácil criar uma lista suspensa de validação de dados entre planilhas dentro de uma pasta de trabalho. Mas se os dados da lista que você precisa para a validação estiverem em outra pasta de trabalho, o que você faria? Neste tutorial, você aprenderá como criar uma lista suspensa de outra pasta de trabalho no Excel em detalhes.
Criar uma lista suspensa pesquisável no Excel
Para uma lista suspensa com numerosos valores, encontrar o adequado não é uma tarefa fácil. Anteriormente, introduzimos um método de autocompletar a lista suspensa ao inserir a primeira letra na caixa suspensa. Além da função de autocompletar, você também pode tornar a lista suspensa pesquisável para aumentar a eficiência no trabalho de encontrar valores adequados na lista suspensa. Para tornar a lista suspensa pesquisável, experimente o método deste tutorial.
Preenchimento automático de outras células ao selecionar valores na lista suspensa do Excel
Digamos que você tenha criado uma lista suspensa com base nos valores no intervalo de células B8:B14. Ao selecionar qualquer valor na lista suspensa, você deseja que os valores correspondentes no intervalo de células C8:C14 sejam automaticamente preenchidos em uma célula selecionada. Para resolver o problema, os métodos deste tutorial irão ajudá-lo.
Melhores Ferramentas de Produtividade para Office
Potencialize suas habilidades no Excel com o Kutools para Excel e experimente uma eficiência sem igual. O Kutools para Excel oferece mais de300 recursos avançados para aumentar sua produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
O Office Tab traz interface com abas para o Office e facilita muito o seu trabalho
- Habilite a edição e leitura com abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas abas na mesma janela, em vez de abrir novas janelas.
- Aumente sua produtividade em50% e reduza centenas de cliques do mouse todos os dias!
Índice
- Habilitando múltiplas seleções
- Usando código VBA
- Usando Kutools para Excel em poucos cliques
- Mais operações
- Permitindo itens duplicados
- Removendo quaisquer itens existentes
- Definindo um separador personalizado
- Definindo um intervalo específico
- Executando em uma planilha protegida
- Artigos Relacionados
- As Melhores Ferramentas de Produtividade para o Office
- Comentários