VBA - Excel - Realizando Consultas Em Um Controle ListBox

20/06/2015 VBAExcelRealizandoConsultasemumcontroleListBox

http://www.macoratti.net/13/10/vba_xlsqry.htm 1/10

VBA Excel Realizando Consultas em umcontrole ListBox

Neste artigo vamos abordar o controle ListBox do VBA que pode ser usado para exibir uma lista deitens sendo muito til para aplicaes VBA. A finalidade do VBA automatizar os recursos quevoc utiliza diariamente em suas aplicaes Office.

O Visual Basic for Applications (VBA) uma implementao doVisual Basic da Microsoft incorporada em todos os programas doMicrosoft Office, bem como em outras aplicaes da Microsoft, comoo Visio, e que foi tambm incorporada pelo menos parcialmente emoutros programas de terceiros como o AutoCAD, Mathcad eWordPerfect. Ele substitui e estende as capacidades de anteriormenteexistentes linguagens de programao de macros especficas para asaplicaes e pode ser usado para controlar a quase totalidade dosaspectos da aplicao anfitri, incluindo a manipulao de aspectosdo interface do usurio tais como menus e barra das ferramentas e otrabalho com formulrios desenhados pelo usurio ou com caixas dedilogo.http://pt.wikipedia.org/wiki/Visual_Basic_for_Applications

Eu estou usando o Microsoft Office Excel 2007 e sistema operacional Windows 7, e portanto ocdigo deste artigo foi testado somente nesta verso e ambiente.

Antes de prosseguir eu sugiro que voc leia os seguintes artigos da seo VBA do site Macoratti.net

VBA Preparando o ambiente para desenvolvimento com o ExcelPlula de VBA Tipos de Aplicao (VBAExcel)VBA Criando um projeto no Excel

Carregando uma ListBox com dados de uma planilha Excel erealizando consultasAbra o Microsoft Excel 2007, e clique no cone para abrir o menu de opes.

Selecione a opo Salvar ou Salvar Como;

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



Este procedimento ir criaruma nova pasta de trabalho (Workbook) em branco contendo trsplanilhas.

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 aplicao VBA com osdados 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 boto Excel no topo/esquerdo de sua tela . Assimusando os dois botes voc pode navegar no VBE para o Excel e, viceversa.

Nota: Voc tambm pode abrir o Editor Visual Basic clicando no menu Desenvolvedor e a seguirno cone Visual Basic.

Vamos incluir no projeto VBA um formulrio ou UserForm, para isso, clique no menu Inserir >UserForm;



Podemos agora a partir da Caixa de Ferramentas selecionar controles e arrastar e soltar noformulrio.

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

Label name=Label1TextBox name=TextBox1CommandButton name=CommandButton1, Caption = PesquisarListBox name = ListBox1CommandButton name=CommandButton2, Caption = Restaurar Dados

Obs: Eu estou usando os nomes padres dos controles neste exemplo por pura preguia dealterar a propriedade name. O correto em um projeto real sempre dar nomes significativosaos controles. Ex: lbDados, cmdPreencher, etc.

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

Altere tambm as seguintes propriedades do UserForm diretamente na janela de propriedades:

BackColor = &H00C0E0FF&Caption = Pesquisa por Nome

Quando o formulrio UserForm1 for aberto ele deve ser preenchido automaticamente com os dadosda planilha Excel.

Este formulrio ser aberto a partir do controle de formulrio Boto que iremos incluir no Excelatravs da ao de uma macro que iremos atribuir ao boto. Ento primeiro temos que criar amacro.

Definindo MacrosO que uma macro?

Uma macro uma coleo de comandos que voc pode aplicar com um nico clique. As macrospodem automatizar quase tudo que seja possvel executar no programa que voc est usando e at



mesmo permitem fazer coisas que talvez voc no soubesse que fossem possveis.

As macros so programao, mas para uslas, voc no precisa ser um desenvolvedor e nemmesmo ter conhecimento de programao. A maioria das macros que voc pode criar nosprogramas 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 srie de aes ouescrevendo a macro. Vamos escrever uma macro que ser usada para abrir o formulrioUserForm1.

Para isso vamos incluir um Mdulo em nosso projeto a partir do menu Inserir > Mdulo;

Aps essa operao digite o cdigo abaixo no mdulo inserido:

SubchamarFormulario() UserForm1.ShowEnd Sub

Este cdigo usa o mtodo Show que exibe um objeto UserForm.

Criamos assim o cdigo em uma Macro pois vamos abrir o formulrio UserForm1 a partir daplanilha Excel.

Vamos aproveitar e criar outra macro no mesmo mdulo. Essa macro ser responsvel porpreencher o ListBox com os dados das planilha Excel. Aqui eu poderia repetir o cdigo mostrado noartigo VBA Excel Usando o controle ListBox mas vou mostrar outra maneira de preencher umcontrole ListBox.

Digite o cdigo abaixo para criar a macro preencherListBox no mdulo:

Sub preencherListBox()

Dim ultimaLinha As LongDim linha As Integer

'retorna ao valor ultima linha preenchidaultimaLinha = Plan1.Range("A90").End(xlUp).Row

'percorre da segunda linha at a ltima linha e atribui o valor da primeira e segunda colunaFor 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 cdigo:

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

Este cdigo obtm ltima linha preenchida pesquisando at a clula A90. No nosso exemplo, comonossa planilha possui somente 7 linhas ser retornado o valor 7 para a varivel ultimaLinha.

2 percorre da segunda linha at a ltima linha e atribui o valor da primeira e segunda colunaFor 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 cdigo percorre a planilha a partir da segunda linha e obtm o valor de cada clula da colunaincluindo 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 clulas da coluna B e atribumos segunda coluna do ListBox.

Agora que j criamos as nossas duas macros vamos uslas. Primeiro vamos abrir o formulrioUserForm1 a partir da planilha Excel.

Retorne planilha Excel e ativando a guia do desenvolvedor vamos incluir um controle deformulrio Boto clicando na opo Inserir e selecionando o controle Boto:

Aps selecionar o Boto coloqueo na planilha Excel. Fazendo isso de imediato ser aberta a janelaatribuir macro, exibindo as duas macros que criamos no Mdulo.

Selecione a macro ChamarFormulario e clique em OK;

Com isso atribumos a macro ChamarFormulario criada ao boto de comando da planilha:

A seguir selecione o boto e altere o nome de Boto 4 para Pesquisar no ListBox;



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

Vamos usar o evento Initialize do formulrio UserForm1 para chamar a segunda macro quecriamos de forma que o formulrio 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 cdigo do formulrio. Selecione o objetoUserForm e o evento Initialize e digite o cdigo abaixo:

Neste cdigo chama a macro preencherListBox que criamos no mdulo.

Pronto ! Alternando para a planilha Excel e clicando no boto Pesquisar no ListBox teremos oformulrio carregado e o listbox preenchido:



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

No evento Click do boto Restaurar Dados vamos apenas chamar a macro preencherListBox parapreencher novamente o Listbox.

Ento comeando com o boto de Pesquisa clique duas vezes sobre o controle CommandButton1 edigite o cdigo abaixo no evento Click:

Private Sub CommandButton1_Click()

'seleciona a primeira celula da planilhaRange("A1").Select'poe o foco no TextBox1TextBox1.SetFocus'define duas variveis para tratar a linha atual e o contadorDim linhaAtual As IntegerDim contador As Integer

'verifica se o TextBox1 diferente () de vazioIf TextBox1.Text "" Then

'atribui o valor zero ao contador contador = 0

'inicia um lao While verificando se o valor da clula diferente do 'TextBox1 e se o contador menor que 20. Enquanto isso for verdade o 'lao 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 clula com o informado e se for igual If ActiveCell.Value = TextBox1.Value Then 'limpa o listbox ListBox1.Clear 'atribuir o valor da clula 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 no foi encontrado MsgBox "Registro no encontrado", vbCritical, "Erro" TextBox1.SetFocusEnd If

End Sub

O cdigo esta comentado e localiza no ListBox um valor informado comparandoo com o valor daclula da planilha.

Agora vamos digitar o cdigo do boto de comando Restaurar Dados.

Clique duas vezes sobre o controle CommandButton2 e digite o cdigo 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 preencherListBoxEnd Sub

O cdigo tambm esta comentado mas basicamente limpa o listbox e preenche novamente ocontrole com os dados da planilha.

Agora vamos retornar planilha Excel e clicar no boto Pesquisar no ListBox.

O formulrio ser aberto e preenchido. Vamos informar um nome e realizar a consulta clicando emPesquisar. Veja abaixo o resultado:

Clicando no boto Restaurar Dados o ListBox ser novamente preenchido.

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

Aguarde novos artigos sobre os controles VBA.



Pegue a planilha aqui : Excel_ListBox_Consulta.zip

Joo 6:55 Porque a minha carne verdadeiramente comida, e o meusangue verdadeiramente bebida.Joo 6:56 Quem come a minha carne e bebe o meu sangue permanece emmim e eu nele.Joo 6:57 Assim como o Pai, que vive, me enviou, e eu vivo pelo Pai,assim, quem de mim se alimenta, tambm viver por mim.

Referncias:

Seo VB .NET do Site Macoratti.netSuper DVD .NET A sua porta de entrada na plataforma .NETSuper DVD Vdeo Aulas Vdeo Aula sobre VB .NET, ASP .NET e C#Seo C# do site Macoratti.netSeo Visual Basic do site Macoratti .netSeo VBA do site Macoratti .netVBA Criando um formulrio de Login (revisitado)VBA Procurando informaes na Planilha (Mtodo Find)VBA Cadastro de Clientes com envio de Email no ExcelExcel Como selecionar clulas/intervalos

Jos Carlos Macoratti

Documents

VBA - Excel - Realizando Consultas Em Um Controle ListBox