"

[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
[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.

Download “Planilha com listagem automática e com auto completar” Lista-com-autocompletar-Pauta-de-promoções.zip – Baixado 149 vezes – 90 KB

Abraço

Marcos Rieper

Deixe uma resposta

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

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.