Selecione vários itens na lista suspensa do Excel – guia completo
As listas suspensas do Excel são uma ferramenta fantástica para garantir a consistência dos dados e facilidade de entrada. No entanto, por padrão, eles restringem a seleção de apenas um item. Mas e se você precisar selecionar vários itens da mesma lista suspensa? Este guia abrangente explorará métodos para permitir seleções múltiplas em listas suspensas do Excel, gerenciar duplicatas, definir separadores personalizados e definir o escopo dessas listas.
- Permitindo itens duplicados
- Removendo quaisquer itens existentes
- Configurando um separador personalizado
- Definir um intervalo especificado
- 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 do Excel.
Usando código VBA
Para permitir múltiplas seleções na lista suspensa, você pode usar Visual Basic para aplicativos (VBA) no Excel. O script pode modificar o comportamento de uma lista suspensa para torná-la uma lista de múltipla escolha. Por favor, faça o seguinte.
Etapa 1: abra o editor de planilha (código)
- 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 guia da planilha e selecione Ver código no menu de contexto.
Etapa 2: use o código VBA
Agora copie o seguinte código VBA e cole-o na janela da planilha de abertura (Código).
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 no futuro.
- Impede que você escolha 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 o Kutools para Excel com apenas alguns cliques
Se você não se sente confortável com o VBA, uma alternativa mais fácil é Kutools for Excel's Lista suspensa de seleção múltipla recurso. Esta ferramenta fácil de usar simplifica a ativação de múltiplas seleções em listas suspensas, permitindo personalizar o separador e gerenciar duplicatas sem esforço para atender às suas diferentes necessidades.
Depois de instalando Kutools para Excel, Vá para o Kutools guia, selecione Lista suspensa > Lista suspensa de seleção múltipla. Então você precisa configurar da seguinte maneira.
- Especifique o intervalo que contém a lista suspensa na qual você precisa selecionar vários itens.
- Especifique o separador para os itens selecionados na célula da lista suspensa.
- Clique OK para completar as configurações.
Resultado
Agora, quando você clica 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 suspensa e clicar no botão "-" para remover os itens que você não deseja mais. Veja a demonstração abaixo:
- Verifique o Quebrar texto após inserir um separador opção se desejar exibir os itens selecionados verticalmente dentro da célula. Se preferir uma listagem horizontal, deixe esta opção desmarcada.
- Verifique o Ativar pesquisa opção se desejar adicionar uma barra de pesquisa à sua lista suspensa.
- Para aplicar este recurso, por favor baixe e instale o Kutools para Excel em primeiro lugar.
Mais operações para lista suspensa de seleção múltipla
Esta seção coleta os diferentes cenários que podem ser necessários ao ativar diversas seleções na lista suspensa 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, experimente 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 SubResultado
Agora você pode selecionar vários itens nas listas suspensas da planilha atual. Para repetir um item em uma célula da lista suspensa, continue selecionando esse item na 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: remova todos os 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 SubResultado
Este código VBA permite selecionar vários itens de uma lista suspensa e remover facilmente qualquer item que você já escolheu. Após selecionar vários itens, caso queira remover algum específico, basta selecioná-lo novamente na lista.
Configurando um separador personalizado
O delimitador é definido como vírgula nos códigos VBA acima. Você pode modificar esta variável para qualquer caractere preferido para usar como separador para as seleções da lista suspensa. Aqui está como você pode fazer:
Como você pode ver, todos os códigos VBA acima têm a seguinte linha:
delimiter = ", "Você só precisa alterar a vírgula para qualquer separador conforme necessário. Por exemplo, você deseja separar os itens por ponto e vírgula, altere a linha para:
delimiter = "; "delimiter = vbNewLineDefinir um intervalo especificado
Os códigos VBA acima se aplicam a todas as listas suspensas da planilha atual. Se desejar 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 maneira.
Como você pode ver, todos os códigos VBA acima têm a seguinte linha:
Set TargetRange = Me.UsedRangeVocê 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 defina as células da lista suspensa como"Desbloqueado"antes de ativar a proteção, garantindo assim que a função de seleção múltipla permaneça ativa após a proteção. No entanto, os códigos VBA mencionados acima não podem funcionar neste caso, e esta seção descreve outro script VBA que é projetado especificamente para lidar com a funcionalidade de seleção múltipla em uma planilha protegida.
Código VBA: habilite a seleção múltipla 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 SubAo habilitar múltiplas seleções nas listas suspensas do Excel, você pode aprimorar bastante a funcionalidade e a flexibilidade de suas planilhas. Esteja você confortável com a 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 seleção múltipla. Com essas habilidades, agora você está equipado para criar documentos Excel mais dinâmicos e fáceis de usar. Para aqueles ansiosos por se aprofundar nos recursos do Excel, nosso site possui diversos tutoriais. Descubra mais dicas e truques do Excel aqui.
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
Índice
- Habilitando múltiplas seleções
- Usando o código VBA
- Usando o Kutools para Excel com apenas alguns cliques
- Mais operações
- Permitindo itens duplicados
- Removendo quaisquer itens existentes
- Configurando um separador personalizado
- Definir um intervalo especificado
- Executando em uma planilha protegida
- Artigos Relacionados
- As melhores ferramentas de produtividade para escritório
- Comentários