VBA - Excel - Realizando Consultas em um controle ListBox

Neste artigo vamos abordar o controle ListBox do VBA que pode ser usado para exibir uma lista de itens sendo muito útil para aplicações VBA. A finalidade do  VBA é automatizar os recursos que você utiliza diariamente em suas aplicações Office.

O Visual Basic for Applications (VBA) é uma implementação do Visual Basic da Microsoft incorporada em todos os programas do Microsoft Office, bem como em outras aplicações da Microsoft, como o Visio, e que foi também incorporada pelo menos parcialmente em outros programas de terceiros como o AutoCAD, Mathcad e WordPerfect. Ele substitui e estende as capacidades de anteriormente existentes linguagens de programação de macros específicas para as aplicações e pode ser usado para controlar a quase totalidade dos aspectos da aplicação anfitriã, incluindo a manipulação de aspectos do interface do usuário tais como menus e barra das ferramentas e o trabalho com formulários desenhados pelo usuário ou com caixas de diálogo.
http://pt.wikipedia.org/wiki/Visual_Basic_for_Applications

Eu estou usando o Microsoft Office Excel 2007 e sistema operacional Windows 7, e portanto o código deste artigo foi testado somente nesta versão e ambiente.

Antes de prosseguir eu sugiro que você leia os seguintes artigos da seção VBA do site Macoratti .net

Carregando uma ListBox com dados de uma planilha Excel e realizando consultas

Abra o Microsoft Excel 2007, e clique no ícone para abrir o menu de opções.

Selecione a opção Salvar ou Salvar Como;

Na janela Salvar Como, informe o nome do projeto : Excel_ListBox_Consulta e altere a opção Tipo para : Pasta de Trabalho Habilitada para Macro do Excel e clique em Salvar:

Este procedimento irá criar uma nova pasta de trabalho (Workbook) em branco contendo três planilhas.

Usando a planilha Plan1 informe alguns dados nesta planilha conforme mostra a figura abaixo.

Neste projeto nosso objetivo será preencher um controle ListBox em uma aplicação VBA com os dados desta planilha e depois realizar consultas no controle ListBox.

A seguir pressione ALT+F11 para abrir o Editor Visual Basic (VBE);

Do VBE você pode ir para o Excel clicando no botão Excel no topo/esquerdo de sua tela . Assim usando os dois botões você pode navegar no VBE para o Excel e, vice-versa.

Nota: Você também pode abrir o Editor Visual Basic clicando no menu Desenvolvedor e a seguir no ícone Visual Basic.

Vamos incluir no projeto VBA um formulário ou UserForm, para isso, clique no menu Inserir -> UserForm;

Podemos agora a partir da Caixa de Ferramentas selecionar controles e arrastar e soltar no formulário.

Vamos selecionar os seguintes controles  arrastando cada um  e soltando no UserForm1 :

Obs: Eu estou usando os nomes padrões dos controles neste exemplo por pura preguiça de alterar a propriedade name. O correto em um projeto real é sempre dar nomes significativos aos controles. Ex: lbDados, cmdPreencher, etc.

Vamos dispor os controles conforme o leiaute abaixo usando o mouse para dimensionar os controles do tamanho desejado.

Altere também as seguintes propriedades do UserForm diretamente na janela de propriedades:

Quando o formulário UserForm1 for aberto ele deve ser preenchido automaticamente com os dados da planilha Excel.

Este formulário será aberto a partir do controle de formulário Botão que iremos incluir no Excel através da ação de uma  macro que iremos atribuir ao botão. Então primeiro temos que criar a macro.

Definindo Macros

O que é uma macro?

Uma macro é uma coleção de comandos que você pode aplicar com um único clique. As macros podem automatizar quase tudo que seja possível executar no programa que você está usando e até mesmo permitem fazer coisas que talvez você não soubesse que fossem possíveis.

As macros são programação, mas para usá-las, você não precisa ser um desenvolvedor e nem mesmo ter conhecimento de programação. A maioria das macros que você pode criar nos programas do Office é escrita em uma linguagem chamada Microsoft Visual Basic for Applications, ou VBA.

Em muitos programas do Office, você pode criar uma macro gravando uma série de ações ou escrevendo a macro. Vamos escrever uma macro que será usada para abrir o formulário UserForm1.

Para isso vamos incluir um Módulo em nosso projeto a partir do menu Inserir -> Módulo;

Após essa operação digite o código abaixo no módulo inserido:

Sub chamarFormulario()
    UserForm1.Show
End Sub

Este código usa o método Show que exibe um objeto UserForm.

Criamos assim o código em uma Macro pois vamos abrir o formulário UserForm1 a partir da planilha Excel.

Vamos aproveitar e criar outra macro no mesmo módulo. Essa macro será responsável por preencher o ListBox com os dados das planilha Excel. Aqui eu poderia repetir o código mostrado no artigo - VBA - Excel - Usando o controle ListBox mas vou mostrar outra maneira de preencher um controle ListBox.

Digite o código abaixo para criar a macro preencherListBox no módulo:

Sub preencherListBox()
Dim ultimaLinha As Long
Dim linha As Integer
'retorna ao valor ultima linha preenchida
ultimaLinha = Plan1.Range("A90").End(xlUp).Row
'percorre da segunda linha até a última linha e atribui o valor da primeira e segunda coluna
For linha = 2 To ultimaLinha
   UserForm1.ListBox1.AddItem Plan1.Range("A" & linha)
   UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = Plan1.Range("B" & linha)
Next

End Sub

Vamos entender o código:

1- ultimaLinha = Plan1.Range("A90").End(xlUp).Row

Este código obtém  última linha preenchida pesquisando até a célula A90. No nosso exemplo, como nossa planilha possui somente 7 linhas será retornado o valor 7 para a variável ultimaLinha.

2- percorre da segunda linha até a última linha e atribui o valor da primeira e segunda coluna
For linha = 2 To ultimaLinha
   UserForm1.ListBox1.AddItem Plan1.Range("A" & linha)
   UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = Plan1.Range("B" & linha)
Next

Este código percorre a planilha a partir da segunda linha e obtém o valor de cada célula da coluna incluindo no ListBox.

A seguir  usamos a propriedade List que  retorna ou define as entradas de lista de um ListBox.

A sintaxe para  List é : objeto.List( linha, coluna ) [= Variante]

Assim obtemos os valores das células da coluna B e atribuímos à segunda coluna do ListBox.

Agora que já criamos as nossas duas macros vamos usá-las. Primeiro vamos abrir o formulário UserForm1 a partir da planilha Excel.

Retorne à planilha Excel e ativando a guia do desenvolvedor vamos incluir um controle de formulário Botão clicando na opção Inserir e selecionando o controle Botão:

Após selecionar o Botão coloque-o na planilha Excel. Fazendo isso de imediato será aberta a janela atribuir macro, exibindo as duas macros que criamos no  Módulo.

Selecione a macro ChamarFormulario e clique em OK;

Com isso atribuímos a macro ChamarFormulario criada ao botão de comando da planilha:

A seguir selecione o botão e altere o nome de Botão 4 para Pesquisar no ListBox;

Agora pressione ALT+F11 para retornar ao Editor Visual Basic (VBE);

Vamos usar o evento Initialize do formulário UserForm1 para chamar a segunda macro que criamos de forma que o formulário seja preenchido com os dados da planilha Excel.

O evento Initialize ocorre depois que UserForm é carregado mas antes dele ser exibido.

Clique no menu Exibir->Codigo para visualizar o código do formulário. Selecione o objeto UserForm e o evento Initialize e digite o código abaixo:

Neste código chama a macro preencherListBox que criamos no módulo.

Pronto ! Alternando para a planilha Excel e clicando no botão Pesquisar no ListBox teremos o formulário carregado e o listbox preenchido:

Agora vamos usar o evento Click do controle CommandButton para que quando o usuário clicar no Pesquisar seja feita uma pesquisa no Listbox e o valor encontrado seja exibido.

No evento Click do botão Restaurar Dados vamos apenas chamar a macro preencherListBox para preencher novamente o Listbox.

Então começando com o botão de Pesquisa clique duas vezes sobre o controle CommandButton1 e digite o código abaixo no evento Click:

Private Sub CommandButton1_Click()
'seleciona a primeira celula da planilha
Range("A1").Select
'poe o foco no TextBox1
TextBox1.SetFocus
'define duas variáveis para tratar a linha atual e o contador
Dim linhaAtual As Integer
Dim contador As Integer
'verifica se o TextBox1 é diferente (<>) de vazio
If TextBox1.Text <> "" Then
  'atribui o valor zero ao contador
  contador = 0

  'inicia um laço While verificando se o valor da célula é diferente do
  'TextBox1 e se o contador é menor que 20. Enquanto isso for verdade o
  'laço irá ser executado
  Do While ActiveCell.Value <> TextBox1.Text And contador < 20
     ActiveCell.Offset(1, 0).Select
    contador = contador + 1
  Loop
End If
  'compara os valores da célula com o informado e se for igual
 If ActiveCell.Value = TextBox1.Value Then
     'limpa o listbox
     ListBox1.Clear
     'atribuir o valor da célula ativa à linhaAtual
     linhaAtual = ActiveCell.Row
    'inclui o valor da linha atual no listbox
     ListBox1.AddItem Plan1.Range("A" & linhaAtual)
     ListBox1.List(ListBox1.ListCount - 1, 1) = Plan1.Range("B" & linhaAtual)
Else
   'o registro não foi encontrado
   MsgBox "Registro não encontrado", vbCritical, "Erro"
   TextBox1.SetFocus
End If

End Sub

O código esta comentado e localiza no ListBox um valor informado comparando-o com o valor da célula da planilha.

Agora vamos digitar o código do botão de comando Restaurar Dados.

Clique duas vezes sobre o controle CommandButton2 e digite o código abaixo no evento Click:

Private Sub CommandButton2_Click()
  'limpa o listbox
  ListBox1.Clear
  'limpa o textbox
  TextBox1.Text = ""
  'atribui o foco ao textbox
  TextBox1.SetFocus
  'chama a macro preencherListBox
  Call preencherListBox
End Sub

O código também esta comentado mas basicamente limpa o listbox e preenche novamente o controle com os dados da planilha.

Agora vamos retornar à planilha Excel e clicar no botão Pesquisar no ListBox.

O formulário será aberto e preenchido. Vamos informar um nome e realizar a consulta clicando em Pesquisar. Veja abaixo o resultado:

Clicando no botão Restaurar Dados o ListBox será novamente preenchido.

Vimos assim mais algumas propriedades do controle ListBox e do VBA Excel para realizar consultas no ListBox.

Aguarde novos artigos sobre os controles VBA.

Pegue a planilha aqui : Excel_ListBox_Consulta.zip

João 6:55 Porque a minha carne verdadeiramente é comida, e o meu sangue verdadeiramente é bebida.

João 6:56 Quem come a minha carne e bebe o meu sangue permanece em mim e eu nele.

João 6:57 Assim como o Pai, que vive, me enviou, e eu vivo pelo Pai, assim, quem de mim se alimenta, também viverá por mim.

Referências:

José Carlos Macoratti