[Excel] Auto completar e Lista automática – Planilha de promoções

Planilha de consulta automática de CNPJ 1 ano de acesso
Planilha de consulta de CNPJ automática – 1 ano de acesso
16 de dezembro de 2017
Planilha de matriz de iniciativas 7 - Guia do Excel
[Excel] Planilha de matriz de prioridades – Fórmula Matricial com PROC e MULT
2 de janeiro de 2018

[Excel] Auto completar e selecionar em Lista de validação – Planilha de promoções

Leia neste artigo como fazer um auto completar e selecionar os dados de uma lista de validação dando muito mais praticidade e velocidade na seleção de dados. O exemplo  utilizado é na construção de uma planilha promoções.

Excel Lista com autocompletar - Pauta de promoções

Há algum tempo criamos o artigo Lista de validação de dados Excel com auto completar VBA, porém não tinha o vídeo e também o autocompletar era chamado sempre que se clicava em uma lista de validação.

Neste artigo você verá como fazer uma macro que seja apenas chamada quando você precisar á partir de um atalho em VBA.

Como criar a validação de dados com auto completar

Criar a lista base da consulta de dados

  1. Crie uma lista com os dados á partir da qual deseja selecionar o item;
  2. Esta lista pode conter os dados apenas que serão selecionados ou já ser uma tabela como no nosso exemplo, no qual temos uma planilha de pauta de promoções;[Excel] Auto completar e selecionar em Lista de validação - Planilha de promoções
  3. No nosso exemplo é uma lista produtos que serão selecionados para a pauta de promoções, no caso serão selecionados portanto somente alguns;

Criar a lista de dados

  1. Clique na sua planilha que contém os dados e clique em Fórmulas, Gerenciador de Nomes e inclua o intervalo;
  2. Usaremos uma fórmula para fazer uma lista que ao incluir registros ela já se ajuste, mas poderia ser apenas selecionados os itens, o problema é que não há um auto-incremento neste caso, ou seja, a lista não se auto-ajusta;
  3. Para resolver o ajuste da altura da lista você pode criar uma tabela e na validação de dados selecionar a coluna dos produtos na tabela;
  4. Outra forma é utilizando uma fórmula de DESLOC, como a fórmula utilizada foi: =DESLOC(‘Lista de produtos’!$B$2;;;CONT.VALORES(‘Lista de produtos’!$B:$B)-1;1); Se quiser aprender como funciona a fórmula DESLOC de forma definitiva veja este artigo DESLOC Excel – Aprenda de uma vez por todas
    [Excel] Auto completar e selecionar em Lista de validação - Planilha de promoções 2

Criar a validação de dados

  1. Clique no local aonde serão inseridas as validações de dados com listas;
  2. Clique na guia Dados->Validação de Dados;
  3. Selecione a opção Permitir: Listas e em Fonte defina aonde está a sua lista de dados. A lista pode ser selecionada ou digitado o nome do intervalo, no nosso caso temos um intervalo nomeado Produtos;[Excel] Auto completar e selecionar em Lista de validação - Planilha de promoções 3
  4. A nossa validação de dados já está pronta, você já consegue selecionar os itens na lista a partir da sua lista de validação, porém, ainda não temos o recurso autocompletar, que iremos inserir agora.

Criando Auto completar Excel

  1. Clique na guia Desenvolvedor, para habilitá-la siga as instruções: http://guiadoexcel.com.br/habilitando-a-guia-desenvolvedor-e-copiando-procedimentos-vba-sub-da-internet;
  2. Clique no botão Modo de Design e no botão Inserir selecione a opção Caixa de Combinação (Controle ActiveX)[Excel] Auto completar e selecionar em Lista de validação - Planilha de promoções 4
  3. Desenhe uma caixa de combinação no Excel em qualquer lugar da planilha;[Excel] Auto completar e selecionar em Lista de validação - Planilha de promoções 6
  4. Na janela que segue altere a propriedade (Name) para TempCombo, conforme a imagem;[Excel] Auto completar e selecionar em Lista de validação - Planilha de promoções 7
  5. Clique nesta lista em Font e altere as suas propriedades de fonte e tamanho;[Excel] Auto completar e selecionar em Lista de validação - Planilha de promoções 8
  6. Altere a propriedade ListRows conforme a quantidade de itens a serem listados de uma vez, sem rolar a barra, por exemplo 8;
  7. Feche a janela Propriedades e clique no botão Modo de Design na guia Desenvolvedor, desmarcando-o.

Inserir código VBA para listar os dados e para o auto completar

  1. Clique na guia Desenvolvedor e no botão Visual Basic;
  2. Na árvore de itens do VBA selecione a planilha aonde está a lista de validação de dados e dê um duplo clique, abrindo a área do código;[Excel] Auto completar e selecionar em Lista de validação - Planilha de promoções 9
  3. Cole o seguinte código fonte na planilha aonde você irá utilizar a sua validação com listar e auto completar
Option Explicit


'Baseado em código da www.contextures.com
Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
        
    'Ocultar caixa de combinação e mover a próxima célula com Enter e Tab
    Select Case KeyCode
        Case 9
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'Nada
    End Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    Set cboTemp = ws.OLEObjects("TempCombo")
    
    cboTemp.Visible = False
End Sub

Public Sub lsChamarAutoPreencher()
    Dim lRng As Range
    Set lRng = ActiveCell

    lsComboAutoPreencher lRng
End Sub

Private Sub lsComboAutoPreencher(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim wsList As Worksheet

    Set ws = ActiveSheet
    Set wsList = Sheets(Me.Name)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Application.CutCopyMode Then
      'Permite copiar e colar na planilha
      GoTo errHandler
    End If
    
    Set cboTemp = ws.OLEObjects("TempCombo")
      On Error Resume Next
      With cboTemp
        .Top = 10
        .Left = 10
        .Width = 0
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Value = ""
      End With
    
    On Error GoTo errHandler
      If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = str
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate
        
        'Abrir a lista suspensa automaticamente
        Me.TempCombo.DropDown
        End If

errHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub

End Sub

4. Clique no botão Fechar do VBA, no canto superior direito, e retorne á pasta de trabalho do Excel.

5. Clique na guia Desenvolvedor e no botão Macros, depois selecione a macro lsChamarAutoPreencher que necessariamente deve estar com o nome da planilha em que quiser incluir a validação de dados com auto completar e autolistar dados.

[Excel] Auto completar e selecionar em Lista de validação - Planilha de promoções 10

Faça o download do arquivo abaixo do exemplo criado no vídeo deste artigo.

Abraço

Marcos Rieper

22 Comments

  1. Aluisio disse:

    Olá Marcos,

    Inicialmente quero parabenizá-lo e dizer que acompanho o site sempre em busca de novidades, que sempre tem me ajudado, em seus artigos.
    Gostaria de saber se tem uma forma de fazer essa pesquisa em um listbox, pois tenho uma planilha mas quero saber se há uma possibilidade de ao começar a digitar um nome já aparecer possíveis resultados.

    Obrigado!

  2. Gleison Alves disse:

    Olá Boa Tarde!

    Primeiramente parabéns pelo trabalho, as suas orientações nos ajuda muito.

    Utilizei este passo a passo e funcionou. O problema é quando compartilho a planilha ela para de funcionar.

    Sabe resolver este problema?

    Att
    Gleison Alves

  3. Aluisio disse:

    Olá Marcos,

    Acompanho sempre as novidades dos artigos do site e quero parabenizá-lo por compartilhar seu conhecimento. Ressaltando que sempre tem me ajudado com as soluções dos vídeos. só uma dúvida existe um possibilidade de fazer um auto-completar em um listbox?

  4. Rafael Modestp disse:

    boa tarde,

    Possivel aplicar o codigo/funcionalidade a várias planilhas de uma pasta de trabalho?

  5. Cristina Maria Pinto da Silva Gaio disse:

    Boa tarde

    Eu tenho uma lista em cascata, tem uma caixa com as categorias e outra com os produtos das categoria, queria que quando escrevo a primeira letra me mostra-se só os itens que começa por essa letra, será possível?? Será que há alguém que me possa ajudar?
    desde já agradeço.

  6. Fernando disse:

    oi, muito bom!
    só não estou conseguindo copiar e colar nenhuma celula na planilha. como resolver?

  7. Julio Lima disse:

    Excelenteeeee !!!
    Gostei muito.

  8. Robson Carmello disse:

    Olá Marcos! Ele só procura pelo início. E se o que for procurado estiver no meio da frase? Tem como ele localizar também?

  9. Luis disse:

    Não consegui fazer no Mac. Tens o passo a passo para o Mac?
    Obrigado

  10. Willian disse:

    Ola! Bom dia,

    Tem como fazer esse auto preencher puxar dados de uma validação de elemento tabela?

  11. Ricardo Silva disse:

    Boa tarde,

    Obrigado por partilhar esta informação.

    Consegui fazer a pesquisa, mas pretendia utilizar de forma automática, ou seja não digitar “Ctrl+Shift+A” .
    Não consegui perceber muito bem no video como alterar isso.

    Será que me pode explicar melhor?

    Obrigado

    Cumprimentos

    Ricardo Silva

  12. Roberto disse:

    Marcos, boa tarde.
    Por favor, por que aconteceu este “erro de compilação”- Membros de dados ou mmétodo não encontrado

    O erro foi aqui, no VALUE:
    .top = 10
    .Left = 10
    ……
    .Visible = false
    . Value = “”

    Poderia me ajudar com a solução?
    No aguardo
    Obrigado

  13. Roberto disse:

    Boa tarde,
    Eu estou usando o Excel para o Mac, última versão 16.20, a ainda surgiu este erro:
    Set cboTemp = ws.OLEObjects(“TempCombo”)

    Para facilitar, Por favor, voces podem me prestar umj serviço de criar uma planilha para minha lista de produtos igual a esta?
    No aguardo

  14. Marina disse:

    Olá Marcos,
    Pra mim, a opção de ‘Ocultar caixa de combinação e mover a próxima célula com Enter e Tab’ não está funcionando.
    Com Ctrl+Shift+A eu consigo digitar no campo em questão, mas a opção some da célula após dar enter e tab. Como posso resolver?

  15. Fabio disse:

    boa tarde
    ótimo suas explicações

    só queria tirar uma duvida ,no campo q vc coloca codigo,coloquei quantidade ,na qual não uso formulas, porque adiciono a quantidade manualmente ,mas essas celulas dessa coluna ficam travadas quando uso cotrl+shift+a nas outras celulas ,e não consigo digitar nelas

    obrigado

  16. Kevin disse:

    Me ajudou muito!! Porém, notei que quando possui fórmula na validação de dados, por exemplo um SE para definir qual lista utilizar, a caixa fica em branco, ou seja, não puxa a lista resultante da fórmula. Sabe como posso solucionar?

    Agradeço desde já.

  17. Evelim disse:

    Boa tarde!
    A função Ctrl+z não funcionam mais, é normal?
    Se não, tem como reverter?
    Muito Obrigada!
    Deus o abençoe!

  18. Julmar Souza disse:

    Boa tarde!

    Achei muito show o recurso e a forma que foi aplicado na aula! Baixei a planilha e ela funciona perfeitamente no meu Excel (20160). Porém, não consegui aplicar o recurso na minha planilha! Quero muito dominar esse recurso! Como posso fazer para ter um suporte na execução desse conteúdo em especifico?

  19. Julmar Souza disse:

    Boa tarde! Achei muito show o recurso! A explicação foi muito TOP! Mas por algum motivo, ou até por vacilo meu, não consegui implementar o recurso de auto completar na minha planilha! Como posso fazer para obter ajuda para conseguir superar esse desafio?

  20. Lucas disse:

    Nao consigo copiar e colar apos executar a macro, tanto na minha planilha quanto na planilha exemplo. Alguma idea do porque?
    obrigado

  21. Daniele disse:

    Olá Marcos! Obrigada por disponibilizar esse recurso. Eu uso uma planilha com várias abas, cada aba um mês, começando por janeiro deu tudo certo e recurso funciona perfeitamente, porém ao tentar implementar o mesmo recurso nas outras abas não dá certo… nem fazendo todo o caminho na outra aba/mês e nem fazendo cópia da aba que deu certo… o que pode estar acontecendo?
    Obrigada!

  22. Erick Vidotto disse:

    Bom dia

    Não teria uma forma de deixar mais automatizado, sem necessario o Ctrl+shif+a?

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.