[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


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 Básico – 1 ano de acesso

R$99,00

COMPRAR
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

7 Comentários

  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.

Deixe uma resposta

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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.