Planilha Filtro automático Excel ao digitar

15

Planilha Filtro automático Excel ao digitar

Neste artigo você verá como criar uma planilha de filtro automático Excel ao digitar. Neste tutorial você verá como criar um filtro de dados tanto para textos quanto para números inteiros, decimais, percentuais e datas e todos os filtros podem ser usados em conjunto.

Criando a lista para o filtro automático no Excel

A lista de dados deve estar no formato de banco de dados, ou seja, com cabeçalhos e os dados da lista devem ser concisos, cada coluna só pode ter aquele tipo de dado.

No nosso exemplo usaremos uma lista de preços de uma cadeia de lojas, mas pode ser aplicado em inúmeras situações, como por exemplo listas de notas fiscais, listas de vendas, listas de contas contábeis entre outras.

Filtro automático Excel ao digitar

Criando os campos de filtros

  1. Aumente a altura do cabeçalho para que você possa inserir os campos aonde serão digitados os filtros.
  2. Clique com o botão direito sobre a guia de funcionalidades do Excel e selecione a opção Personalizar a faixa de opções e marque a opção Desenvolvedor no lado direito caso esteja desmarcada.
  3. Na guia Desenvolvedor, selecione a opção Inserir e insira o componente Caixa de texto.Filtro automático Excel ao digitar
  4. Desenhe sobre cada uma das colunas esta caixa de texto que será utilizada para o filtro de dados automático no Excel e ajuste o tamanho dos mesmos, coloque dois no campo Intervalo.Filtro automático Excel ao digitar
  5. Verifique se o botão Modo de Design na guia Desenvolvedor está marcado e clique com o botão direito sobre o botão acima do campo código e clique em Propriedades. Nesta aba de configurações altere a propriedade Name para Codigo, ou outro nome de acordo. Repita a operação para todos os textbox. Nós colocamos os seguintes nomes para os textBox sequencialmente da esquerda para a direita.
    1. Codigo
    2. Loja
    3. Produto
    4. Estoque
    5. PrecoProduot
    6. Comissao
    7. DataDe
    8. DataAte
  6. Clique sobre o cabeçalho da planilha e clique em Dados e marque a opção Filtro.

Agora você já tem os campos todos os campos que usaremos para programar o filtro automático n Excel e também a planilha já tem o filtro marcado sobre o mesmo.

Na próxima etapa iremos programar conforme cada campo como o mesmo irá filtrar os dados.

Programando o filtro automático no Excel

Para realizarmos a programação o botão Modo Design na guia Desenvolvedor deverá estar ativo, pois desta forma você estará editando os objetos de formulários do Visual Basic dentro do Excel.

  • Dê um duplo clique sobre o campo criado Codigo, o sistema irá criar automaticamente uma função da seguinte forma.Filtro automático Excel ao digitar
    Este evento criado é disparado quando o campo Codigo for alterado, ou seja, quando for digitado no textBox Codigo na planilha, todos os códigos que estiverem neste procedimento serão executados.
  • Cole o seguinte código no procedimento. Este código irá realizar o filtro de dados do tipo Inteiro, veja que o código no início verifica se o campo está diferente de vazio e se estiver insere um filtro do primeiro campo (Field), com o critério (Criteria1), no qual o filtro é igual campo digitado no textBox Codigo (“=” & Codigo.Text). Utilize este filtro para campos inteiros e com busca exata, ou seja somente aquele código, lembrando de substituir o Codigo.Text pelo nome do seu textBox. Teste o filtro automático Excel ao digitar voltando no Excel e desmarcando a opção Modo Design na guia Desenvolvedor.
Private Sub Codigo_Change()
  If Codigo.Text <> "" Then
    Selection.AutoFilter Field:=1, Criteria1:="=" & Codigo.Text
  Else
    Selection.AutoFilter Field:=1
  End If
End Sub
  • Volte para a planilha clicando para fechar o ambiente VBE no canto superior direito, botão X da janela.
  • Clique duas vezes sobre o textBox sobre o campo Loja e no campo gerado cole o seguinte código fonte dentro do procedimento.
Private Sub Loja_Change()
    Selection.AutoFilter Field:=2, Criteria1:=CStr("*" + Loja.Text + "*")
End Sub

Este código realiza o filtro de colunas de texto, campos do tipo string, veja que o campo de filtro é o Field:=2, ou seja, segunda coluna da tabela, referente á loja, o critério de filtro (Criteria1) é que seja igual á CStr(“*” + Loja.Text + “*”), o CStr faz a conversão de qualquer valor para Texto, e o + concatena o texto, então se tiver digitado 5 no campo loja, ele ficará “*5*” e assim o filtro buscará na coluna Loja qualquer valor que tenha 5 no seu campo.

  • Volte para o Excel e clique duas vezes sobre o textBox Produto, o mesmo irá criar um código automaticamente. Substitua o mesmo pelo código abaixo:
Private Sub Produto_Change()
    Selection.AutoFilter Field:=3, Criteria1:=CStr("*" + Produto.Text + "*")
End Sub

Este código é semelhante ao filtro da coluna Loja, com as mudanças do Field ser a coluna 3, equivalente aos produtos e no critério de filtro utilizar o TextBox Produto.Text ao invés do Loja.Text.  Teste o filtro automático Excel ao digitar voltando no Excel e desmarcando a opção Modo Design na guia Desenvolvedor.

  • Da mesma forma clique duas vezes sobre o TextBox equivalente ao campo de Estoque e digite o código a seguir.
Private Sub Estoque_Change()
  If Estoque.Text <> "" Then
    Selection.AutoFilter Field:=4, Criteria1:="=" & Estoque.Text
  Else
    Selection.AutoFilter Field:=4
  End If
End Sub

Veja que no código o sistema está verificando se o TextBox Estoque é diferente de vazio e inserindo o filtro neste caso, para a coluna 4 e o critério de que o valor do estoque seja exatamente o digitado, então este filtro automático no Excel é feito por um número inteiro e não aproximado. Teste o filtro automático Excel ao digitar voltando no Excel e desmarcando a opção Modo Design na guia Desenvolvedor.

  • Volte para o Excel e dê um duplo clique sobre o TextBox Preco, alterando o código para o seguinte.
Private Sub PrecoProduto_Change()
  If PrecoProduto.Text <> "" Then
    Selection.AutoFilter Field:=5, Criteria1:=">=" & Replace(PrecoProduto.Text, ",", "."), Criteria2:="<=" & Replace(PrecoProduto.Text + 0.0099999, ",", ".")
  Else
    Selection.AutoFilter Field:=5
  End If
End Sub

Este filtro também identifica se o campo PrecoProduto está diferente de vazio e se estiver faz uma busca pelo valor. Neste caso para o filtro de Decimal é necessário usar dois critérios Criteria1 e Criteria2, aonde o valor digitado com vírgula é convertido para ponto por causa do formato americano e é utilizado >= no primeiro critério e <= no segundo para que os dados sejam filtrados dentro destes valores.

Veja que no segundo critério é aumentado o valor em 0.00999999, para que o filtro funcione corretamente.

  • Retorne ao Excel e clique duas vezes sobre o TextBox Comissao e altere o seu código fonte conforme a seguir.
Private Sub Comissao_Change()
  If Comissao.Text <> "" Then
    Selection.AutoFilter Field:=6, Criteria1:=">=" & Replace(Comissao.Text / 100, ",", "."), Criteria2:="<=" & Replace(Comissao.Text / 100 + 0.0099999, ",", ".")
  Else
    Selection.AutoFilter Field:=6
  End If
End Sub

Este filtro é percentual, por isso o filtro é quase igual ao Decimal, anterior a este, porém note que o valor no código é dividido por 100, para converter o mesmo em percentual, de modo que ao digitar 50 o sistema entenda que é 50% e não 5000%, e também não seja necessário digitar 0,5. Lembre sempre que se o nome do seu campo for diferente você deve mudar todos os códigos que tenham .Text. Teste o filtro automático Excel ao digitar voltando no Excel e desmarcando a opção Modo Design na guia Desenvolvedor.

  • Voltando ao Excel dê um duplo clique sobre o campo DataDe e digite o seguinte código.
Private Sub DataDe_Change()
  If DataDe.Text <> "" And IsDate(DataDe.Text) And IsDate(DataAte.Text) Then
    Selection.AutoFilter Field:=7, Criteria1:=">=" & Format(DataDe.Text, "mm/dd/yyyy"), Operator:=xlAnd, Criteria2:="<=" & Format(DataAte.Text, "mm/dd/yyyy")
  Else
    Selection.AutoFilter Field:=7
  End If
End Sub
 
Private Sub DataAte_Change()
  If DataDe.Text <> "" And IsDate(DataDe.Text) And IsDate(DataAte.Text) Then
    Selection.AutoFilter Field:=7, Criteria1:=">=" & Format(DataDe.Text, "mm/dd/yyyy"), Operator:=xlAnd, Criteria2:="<=" & Format(DataAte.Text, "mm/dd/yyyy")
  Else
    Selection.AutoFilter Field:=7
  End If
End Sub

Este código identifica se o campo DataDe é diferente de vazio e se os campos DataDe e DataAte são datas, para evitar erros de filtro.

Também note que o código é filtrado entre as datas utilizando os operadores >= e <= e também que a data é convertida para o formato americano pela utilização da função Format e da mascará “mm/dd/yyyy”, fazendo com que a data 31/10/2010 seja formatada para 10/31/2010 por exemplo.  Teste o filtro automático Excel ao digitar voltando no Excel e desmarcando a opção Modo Design na guia Desenvolvedor.

  • Volte ao Excel e no Campo DataAte digite também o mesmo código, pois o mesmo filtro é aplicado digitando em um e em outro.

Lembre que para utilizar o sistema não deve estar no Modo Design.

DIGITE O SEU EMAIL PARA FAZER O DOWNLOAD DOS ARQUIVOS:

Seu nome (obrigatório)

Seu e-mail (obrigatório)

Abraço

Marcos Rieper

15 COMENTÁRIOS

  1. Marcos, bom dia.

    Site fantástico, parabéns!
    Gostaria de saber como insiro um TextBox direto na planilha ja que está sem Userform, pois na tentativa de inserir direto na planilha uma mensagem de erro aparece “Não é possivel inserir objeto”.

    Desde já obrigado.

  2. Boa tarde Marcos!
    Primeiramente quero parabeniza-lo pelo site e agradecer pela quantidade de material e vídeos disponibilizados.

    Bem, preciso usar esta macro com a planilha protegida. É possível ?

    Grato.

  3. Olá Marcos, como faço para excluir definitivamente campos da tabela dinâmica? só encontrei opção para remover, os que quero retirar eu criei em Campo Calculado.

  4. Boa noite marcos,

    Como poderia fazer para filtrar, dando resultados parciais, ao digitar em uma coluna, parte de um valor numérico, tipo um CPF ou CNPJ? Obrigado!

  5. gostei muito dos seus videos, eu gostaria de saber se em um filtro de tabela tem como colocar um botão para alterar as informações filtradas e salvar. ex eu faço um filtro e no filtro eu alterar a informação e salvar na planilha original.

  6. Olá boa tarde,
    Não consigo que funcione. Dá a seguinte msg: “Run time error 1004: Não foi possível executar o método AutoFilter da classe Range”. Utilizei a “caixa de texto (controlo activeX). É pena, porque é interessantíssimo.
    uito obrigado se me puder ajudar.

DEIXE UMA RESPOSTA

Please enter your comment!
Please enter your name here