Auto completar em Excel VBA

Criando log no Excel VBA - Nome do usuário VBA, Data e hora VBA
Criando log no Excel VBA – Nome do usuário VBA, Data e hora VBA
29 de setembro de 2012
Como criar um Gráfico de Box-Plot Excel
Como criar um Gráfico de Box-Plot Excel
6 de novembro de 2012

Auto completar em Excel VBA

Objetivo: Disponibilizar um exemplo de como podemos fazer um auto completar para listas em Excel.


 O recurso de auto completar no Excel é limitado ao preenchimento automático apenas quando a lista de palavras está acima da palavra que está sendo digitada atualmente.

 Neste artigo é disponibilizado um exemplo de como utilizar o código VBA e o Excel para que possamos auto completar dados com o uso de um formulário e depois jogar este resultado diretamente na célula ativa do Excel.

  Para tanto você pode pressionar o botão que coloquei no formulário, ou ainda melhor, utilizar o atalho CTRL+SHIFT+a que coloquei atribuído ao método que chama o formulário.

  O código completo pode ser visto clicando na guia Desenvolvedor e clicando sobre o objeto do formulário.

'Digite aqui o intervalo a ser autocompletado
Private Const r As String = "A1:A100"
Private sInput As String

'Faz parar a pesquisa dos dados digitados
Dim flParar As Boolean

'Ao digitar deletar ou backspace o sistema limpa a variável de controle para pesquisar novamente
Private Sub txtInput_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    'Limpa a variável de controle
    If (KeyCode = vbKeyBack) Or (KeyCode = vbKeyDelete) Then
        flParar = True
    Else
        flParar = False
    End If
        
    If (KeyCode = 13) Then
        ActiveCell.Value = UserForm1.txtInput.Text
        UserForm1.txtInput.Value = vbNullString
        UserForm1.Hide
    End If
 
End Sub
 
'Faz a busca das palavras
Private Sub txtInput_Change()
    Dim lPalavra As String
    
    If flParar Then
        flParar = False
    Else
        sInput = Left(Me.txtInput, Me.txtInput.SelStart)
        lPalavra = GetFirstCloserWord(sInput)
        If lPalavra & ""  "" Then
            flParar = True
            Me.txtInput.Text = lPalavra
            Me.txtInput.SelStart = Len(sInput)
            Me.txtInput.SelLength = 999999
        End If
    End If
    
End Sub

'Seleciona a primeira letra
Private Function GetFirstCloserWord(ByVal Word As String) As String
    Dim c As Range
    
    For Each c In ActiveSheet.Range(r).Cells
    If LCase(c.Value) Like LCase(Word & "*") Then
            GetFirstCloserWord = c.Value
            Exit Function
        End If
    Next c
    Set c = Nothing
 
End Function

Veja como ficou o formulário:

Para utilizar este formulário você deve clicar com o botão direito sobre o formulário UserForm1 e em Exportar arquivo…, clique depois em Módulo1 e Exportar arquivo…

Feitas estas exportações você deve ir na guia Desenvolvedor com a sua planilha nova aberta e depois clique em qualquer objeto na guia Desenvolvedor e em importar conforme a imagem.

 

Agora altere o local aonde a lista dos dados estará ativa, o intervalo “A1:A100”.

‘Digite aqui o intervalo a ser autocompletado
Private Const r As String = “A1:A100”

[saiba_mais]

Na planilha você pode atribuir o procedimento Botão1_Clique ao atalho que preferir no teclado clicando na Guia Desenvolvedor->Macros e selecionando o procedimento Botão1_Clique.

GUT PPT

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

26 Comentários

  1. Parabéns Marcos, como sempre suas dicas são muito valiosas, o site é maravilhoso!

  2. Anderson disse:

    Ótimo site e excelente código! Como faço pra alterar o intervalo a ser autocompletado para uma guia diferente da guia atual?

  3. Anderson disse:

    Consegui encontrar a referência do intervalo do autocomplete
    “For Each c In ActiveSheet.Range(r).Cells” e alterei sheets.

  4. Fstefen disse:

    Meu excel é 2003, como nao pude exportar criei o formulário na minha planilha e copiei o código, mas qndo chamo a macro carrega o formulário porém o auto complete não funciona. Vcs poderiam me ajudar?

  5. Anderson disse:

    Fstefen,
    Acho que vc deve editar a linha
    “For Each c In ActiveSheet.Range(r).Cells” para
    “For Each c In sheets(“Nome Guia onde esta a lista”).Range(r).Cells”, levando em consideração que vc tenha colocador sua lista de dados entre as células “A1:A100”.

  6. Johnny disse:

    Amigo, existe um jeito de fazer com que o excel traga todos os nomes de uma lista que contenham o nome que eu estou digitando em uma célula?

    Ex. digito Carlos e o excel traz: Carlos Alberto; Felipe Carlos; Carlos joaquim….

    Obrigado o site é 10.

  7. fRANCISCO disse:

    Marcos, nota 10, eu não sei nada de vba e por isso faço a seguinte pergunta: Eu criei uma agenda telefônica onde na planilha 1 eu faço a pesquisa e preencho os campos com procv, até ai blz, só que os dados estão na planilha 2, como eu uso a função que você criou sendo que ele vai colsultar na planilha 2 e não na 1, HELP!!!

  8. Wilyoya disse:

    Excelente e muito útil!
    Muito obrigado”

  9. joao disse:

    Amigo, existe a possibilidade de na caixa de formulário aparecer todos os nomes que começam com as letras que você for informando?
    Obrigado
    Sempre recorro a seu site por coisas novas. Obrigado

  10. cristian disse:

    Olá amigo, muito bom seu post.
    Tenho um problema:
    ele não reconhece as linhas:
    36 – If lPalavra & “” <> “” Then
    51 – If LCase(c.Value) Like LCase(Word & “*”) Then

    Obrigado!!

  11. Adriano disse:

    Como faço para utilizar a referencia da lista “Private Const r As String = “A1:A100″ ” sendo de uma outra planilha? já tentei o seguinte:

    Private Const r As String = ActiveWorkbook.Worksheets(“Plan1”).Range(“A1:A4000”)

    porém dá um erro de compilação e informa “Expressão constante obrigatória”

    você pode me ajudar?

  12. Adriano disse:

    Marcos,

    O que preciso é:

    Esse é o roiginal:
    Private Const r As String = (“e100001:e200000”)

    Esse eu coloquei dessa forma
    Private Const r As String = (“‘Base_Filtros’!f2:f20000″)

    porém deu erro no código:”ERRO DE DEFINIÇÃO NO ARQUIVO OU DEFINIÇÃO DO OBJETO”

    ‘Seleciona a primeira letra
    Private Function GetFirstCloserWord(ByVal Word As String) As String
    Dim c As Range

    For Each c In ActiveSheet.Range(r).Cells [NESSA LINHA]
    If LCase(c.Value) Like LCase(Word & “*”) Then
    GetFirstCloserWord = c.Value
    Exit Function
    End If
    Next c
    Set c = Nothing

    End Function

    tem como ajustar?

  13. Kenner Sávio disse:

    Boa tarde Marcos.

    Poderia me ajudar numa dúvida?

    Fico no aguardo.

    Atenciosamente.

  14. JOAO PAULO disse:

    Olá Marcos, esse recurso autocompletar funciona nas minhas células com validação de dados, porém sem impedir que o usuário digite um nome diferente do que está na lista de dados, não reconhecendo a regra “parar” que coloquei na validação de dados…. poderia me ajudar ?

  15. Anderson Leal disse:

    Muito boa a dica, vai me ajudar bastante.

  16. Thiago disse:

    Olá Marcos, sua dica é muito boa e vai me ajudar muito. Você poderia me ajudar a fazer a Macro pegar a informações de nome em outra aba do Excel?

    • Marcos Rieper disse:

      Boa tarde Thiago,

      Você pode usar esta função: =DIREITA(CÉL(“nome.arquivo”);NÚM.CARACT(CÉL(“nome.arquivo”))-LOCALIZAR(“]”;CÉL(“nome.arquivo”))).

      Ela retorna apenas se a planilha estiver salva. Para buscar de outras planilha, altere após os parâmetros “nome.arquivo”, incluindo “;” e a selecionando uma célula da planilha desejada.

      Abraço

      Marcos Rieper

  17. Tchago Brito disse:

    Como faço para travar em uma sheet específica, para não precisar deixar sempre meus resultados de busca visíveis?

  18. paulo denis disse:

    olá marcos, parabens pelo trabalho

    como fazer este método em uma célula? sem ser em um userform. Quero começar a digitar em uma celula e ele buscar o autopreenchimento em uma lista.

    Abs

  19. Renan disse:

    Marcos,

    Como pegar o valor que está em outra célula de acordo com essa pesquisa?
    Ex: Pesquisei por “Coca Cola” quero que ele traga o valor da coca cola, que está na coluna do lado.

    Obrigado!

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.