VBA Excel - Combobox em cascata em um UserForm

Novembro 2016



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)

Tradução feita por Lucia Maurity y Nouira

Veja também :
Este documento, intitulado « VBA Excel - Combobox em cascata em um UserForm »a partir de CCM (br.ccm.net) está disponibilizado sob a licença Creative Commons. Você pode copiar, modificar cópias desta página, nas condições estipuladas pela licença, como esta nota aparece claramente.