VBA Excel - Combobox em cascata em um UserForm

Faça uma pergunta


Introdução

Esta dica responde a uma discussão no fórum e foi feita depois de estudar esta outra dica.
Trata-se de um método, entre outros, para preencher uma combobox com escolhas que dependem de outras, com base na técnica indireta do Excel.

Pré-requisitos

  • 1 Pasta,
  • 1 UserForm (formulário),
  • 3 combobox.

Se você quiser, a pasta disponível para download vem "pronta"...

Gerir nomes no Excel

Para definir nomes em uma pasta do Excel, existem duas opções de acesso:
  • Versões do Excel < 2007 => Inserir/Nomes/Definir
  • Versões > 2007 : Faixa Fórmulas / Definir um nome.

Para isso, consulte o tutorial mencionado acima aqui.

O código das combobox

Nós queremos, durante o download do UserForm que, apenas a ComboBox1 seja preenchida. As outras, dependentes da escolha feita na primeira, devem ficar vazias.

Preenchimento da primeira lista

Para preencher uma combobox com o conteúdo da faixa "Nomeada" na pasta, a sintaxe é:
ComboBox1.List = Application.Transpose(Range("meuNome"))
Para esvaziar uma ComboBox :
Combobox1.Clear
O que nos dá o código para preencher a primeira lista suspensa, durante o download do UserForm :
Private Sub UserForm_Initialize()
ComboBox1.Clear
ComboBox1.List = Application.Transpose(Range("Dep"))
ComboBox2.Clear
ComboBox3.Clear
End Sub

Preenchimento da segunda lista

Quando um valor é selecionado na primeira lista drop-down, esta escolha (o texto indicado na ComboBox) corresponde ao nome de uma pasta. Assim, precisamos apenas mostrar o conteúdo das células desta faixa nomeada.
Para isso, vamos usar o evento Change da primeira lista suspensa, como este:
Private Sub ComboBox1_Change() 'Combobox departemento
'aqui evitamos um bug caso o usuário tenha deletado o conteúdo do ComboBox1
If ComboBox1.Value = "" Then Exit Sub
ComboBox2.Clear
ComboBox3.Clear
ComboBox2.List = Application.Transpose(Range(NomeFaixa))
End Sub


O mesmo vale para a terceira que está em função da segunda:
Private Sub ComboBox2_Change() 'Combobox comuns
If ComboBox2.Value = "" Then Exit Sub
ComboBox3.Clear
ComboBox3.List = Application.Transpose(Range(NomeFaixa))
End Sub

Bugs possíveis

Faixa não nomeada

O nome digitado na ComboBox (caixa de combinação) não corresponde a nenhum nome de pasta, porque ele ainda não foi definido.
Para resolver esse problema, vamos criar uma pequena função. Seu papel será o de percorrer todos os nomes da pasta de trabalho e verificar se o nome digitado é o nome de uma faixa nomeada desta pasta.
O código para essa função:
Function NomeDefinido(Nom As String) As Boolean
Dim Nomes As Name
NomeDefinido = False
For Each Nomes In ThisWorkbook.Names
    If Nomes.Name = Nome Then NomeDefinido = True: Exit Function
Next Nomes
End Function

Erro de digitação

Como você pode ver no exemplo em download, a definição de nomes não leva em conta nem caracteres especiais, nem espaços.
Assim sendo, às vezes, é preciso fazer pequenas alterações nas variáveis, para "colar" tudo.
Veja este exemplo:
Function CaracSpec(Nom As String) As String
CaracSpec = Replace(Nome, " ", "_")
CaracSpec = Replace(CaracSpec, "-", "_")
End Function

Código na íntegra

Este código é o código do exemplo posto em download.
Option Explicit

Private Sub UserForm_Initialize()
ComboBox1.Clear
ComboBox1.List = Application.Transpose(Range("Dep"))
ComboBox2.Clear
ComboBox3.Clear
End Sub

Private Sub ComboBox1_Change() 'Combobox departamento
If ComboBox1.Value = "" Then Exit Sub
ComboBox2.Clear
ComboBox3.Clear
Dim NomeFaixa As String
NomeFaixa = CaracSpec(ComboBox1.Value)
If NomeDefinido(NomeFaixa) Then
    ComboBox2.List = Application.Transpose(Range(NomeFaixa))
Else
    ComboBox2.AddItem """Nenhuma comum"""
End If
End Sub

Private Sub ComboBox2_Change() 'Combobox comuns
If ComboBox2.Value = "" Then Exit Sub
ComboBox3.Clear
Dim NomeFaixa As String
NomeFaixa = CaracSpec(ComboBox2.Value)
If NomeDefinido(NomeFaixa) Then
    ComboBox3.List = Application.Transpose(Range(NomeFaixa))
Else
    ComboBox3.AddItem """Nenhuma rua"""
End If
End Sub

Function NomeDefinido(Nom As String) As Boolean
Dim Nomes As Name
NomeDefinido = False
For Each Noms In ThisWorkbook.Names
    If Nomes.Name = Nome Then NomeDefinido = True: Exit Function
Next Nomes
End Function

Function CaracSpec(Nome As String) As String
CaracSpec = Replace(Nome, " ", "_")
CaracSpec = Replace(CaracSpec, "-", "_")
End Function

Download

Esta pasta retoma este exemplo de Raymond PENTIER adicionando os nomes das ruas de duas cidades.
Link de download da pasta (em francês)
Jean-François Pillou

Nosso conteúdo é produzido em colaboração com especialistas em tecnologia da informação sob o comando de Jean-François Pillou, fundador do CCM.net e diretor digital do Grupo Figaro. CCM é um site sobre tecnologia líder em nível internacional e está disponível em 11 idiomas.

Mais informações sobre a equipe do CCM