CRIAR, ACTUALIZAR E ORDENAR UMA LISTA DE VALIDAÇÃO (Create, Update and Sort a Validation List)

Criar uma lista de validação é, para a maior parte dos utilizadores de Excel, uma tarefa muito simples de assimilar. No entanto, a rotina proporcionada pelo Excel tem limitações, dado que a única forma de acrescentarmos novos itens é introduzi-los manualmente e reconfigurar a lista. Por sua vez, a sua reordenação alfabética tem de ser também manual. Aqui está um pequeno exemplo de uma lista de validação que pode ser aumentada e, à medida que vão sendo acrescentados novos itens, estes são automaticamente listados por ordem alfabética.
Create a validation list is, for most Excel users, a simple and easy task. However, the routine provided by Excel has limitations, since the only way to add new items is to enter manually and reset the list. In the same way, their alphabetical reordering must also be manual. Here is a small example of a validation list that can be increased and, as new items are being added, they are automatically listed in alphabetical order.



CRIAR LISTA E DEFINIR NOME
Create and Name a List
  1. Nomear a Folha1 como "Data" e a Folha2 como "Listas", por exemplo;
  2. Na Coluna "A" da folha "Listas" vamos criar uma lista qualquer (nomes próprios, por exemplo);
  3. A seguir, Definimos o nome da lista - Ir para Fórmulas, Definir Nome (atribuir um nome - Names - e seleccionar TODA a coluna "A");
  4. Seleccionamos depois a célula "D11" da folha "Data" e criamos uma lista de validação - Ir para Dados, Validation Data, Definições, escolhemos Lista, em Origem introduzimos "=Nome que atribuímos à lista (ver ponto 2);
  1. Name the Sheet1 as "Data" and Sheet2 as "Listas", for example;
  2.  In Column "A" of sheet "Listas" create a list (names, for example)
  3. Next, define the list name - Go to Formulas, Define Name (assign a name - Names - and select the entire column "A")
  4. Then select "D11" in "Data" sheet and create a validation list - Go to Data, Data Validation, Settings, choose List, and in Source insert "= name we attach to the list (see #2);

CÓDIGO VBA
VBA Code

Na folha "Data", clicar com o botão direito do rato em cima do separador e escolher "Ver Código";
Introduzir o seguinte código
In "Data" sheetright click mouse button over the tab and choose "View Code";
Enter the following code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Listas")
If Target.Column = 4 And Target.Row > 10 Then
  If Application.WorksheetFunction.CountIf(ws.Range("Names"), Target.Value) Then
    Exit Sub
  Else
    i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
    ws.Range("A" & i).Value = Target.Value
    ws.Range("Names").Sort Key1:=ws.Range("A1"), _
      Order1:=xlAscending, Header:=xlGuess, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
  End If
End If
End Sub

Atenção: Column = 4 And Target.Row > 10 é o parâmetro que define a célula D11 como a primeira célula que a macro vai afectar.
Note: Column = 4 And Target.Row> 10 is the parameter that defines the cell D11 as the first cell that will affect by macro.

Na folha "Listas" clicar com o botão direito do rato em cima do separador e escolher "Ver Código";
Introduzir o seguinte código
In the "Listas" right click mouse button over the tab and choose "View Code";
Enter the following code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
  Header:=xlGuess, OrderCustom:=1, _
  MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Para testar o correcto funcionamento, basta introduzir um novo item (nome) na célula "D11". Em "Listas" verificamos que o novo item foi automaticamente acrescentado e a lista foi ordenada alfabeticamente.
Para criar uma tabela com listas de validação iguais - por exemplo "D11:D17", basta copiar a célula "D11" para as células seguintes.
To test operation, simply enter a new item (name) in cell "D11". In "Lists" we found that the new item was added automatically and the list was ordered in alphabetic.
To create a table with the same validation - eg "D11: D17," just copy "D11" to the cells below.

Comentários

  1. Alcides-amor1957@hotmail.com27 de julho de 2012 às 19:55

    João, Na verdade gostaria de fazer uma pergunta. E se na minha lista nomeada eu tivesse 01-Maria e gostaria que aparecesse assim na lista suspensa, mas na célula fosse adicionado somente o número 01. como eu procederia ?? vc teria um exemplo ??

    ResponderEliminar
  2. Alcides, Obrigado pelo seu interesse.
    Uma forma simples de responder à sua questão é usar uma fórmula noutra célula. Por exemplo, na célula F11:
    =ESQUERDA(D11,2)
    D11 mostra a escolha na lista de validação e "2" o número de caracteres que queremos separar.
    Assim, na lista de validação (D11) aparecerá "01-Maria" e na célula E11 aparecerá apenas "01".
    Espero ter ajudado
    João

    ResponderEliminar
  3. Olá Sr. João, tudo bem!?
    Tenho uma dúvida: Sempre que tento validar dados referenciando uma lista de outra Plan, não consigo conforme o método apresentado acima. Neste caso, a forma que encontrei é inserir na caixa "Fonte:" é =INDIRETO("Listas!Names").
    Não consegui executar a macro. E caso consiga aprender, será de grande utilidade para mim.
    Desde já agradeço!
    Excelente Post.

    ResponderEliminar
    Respostas
    1. Caro Alexandre, Obrigado pelo seu interesse.
      Na verdade, estou a ter alguma dificuldade em perceber a sua dúvida.
      Quando diz que não consegue "referenciar uma lista de outra Plan" está a referir-se a outra planilha ou a outra plan da mesma planilha?
      Quanto a não conseguir executar a macro, provavelmente não tem as macros activadas.
      Para o efeito, caso a sua versão seja a 2007, clique no botão do office (canto superior esquerdo da planilha do excel) > escolha "Opções do Excel" > Centro de Fidedignidade > Definições do Centro de Fidedignidade > Definições das Macros > Activar todas as macros. (Não estou certo se a versão brasileira do Office tem a mesma nomenclatura que a de Portugal. Pode por isso haver algumas diferenças nos nomes dos menus).
      Espero ter ajudado.
      João

      Eliminar
    2. Boa tarde Sr. João!
      Agradeço a rapidez em responder minha dúvida.
      Quando digo plan, quero dizer a outra Plan da mesma Pasta de trabalho. Mas executando cada operação novamente conforme orientado acima, percebi que meu erro foi nomear a coluna apenas selecionado-a e nomeando-a em "Caixa de Nome" ao lado esquerdo da "Barra de Fórmulas". Agora, estou tentando executar a macro.
      Copiei o código para ambas as plans e salvei a Pasta de trabalho habilitada para macro. Quando clico em Macros (na aba Desenvolvedor), não é listada macro alguma. Não sei como executar o código.
      Será que sabes o que pode ser?
      Desde já, muito obrigado pela atenção!

      Eliminar
    3. Caro Alexandre
      Não tem que executar a macro. Ela executa automaticamente, dado que o objectivo é reordenar a lista sempre que um novo item é adicionado. Experimente, por exemplo, adicionar um novo item na tabela da planilha "Data". verificará depois que na planilha "Listas" o novo item está incluído e ordenado alfabeticamente.
      Ao seu dispor
      João

      Eliminar
    4. Bom dia Sr. João, tudo bem?!
      Habilitei a Macro, mas ainda assim, adicionando novos nomes, a lista não atualiza.
      Não aproveitando de sua imensa atenção, seria possível, por gentilza, enviar este arquivo para o meu e-mail: minoale@hotmail.com.
      Grato!

      Eliminar
    5. Caro João!
      Agradeço o envio do arquivo.
      Até o próximo contato.
      Cordialmente,

      Eliminar

Enviar um comentário

Mensagens populares deste blogue

EXCEL 2007 - FUNÇÕES PARA CALCULAR HORAS (Functions to Calculate Hours)

CONVERTER HORAS EM DIAS DE TRABALHO (Convert Hours to Workdays)

VBA EXCEL - OCULTAR LINHAS EM BRANCO (Hide Blank Rows)