Colar em uma planilha obedecendo a validação de dados

Question

Boa tarde!

Tenho uma planilha em excel (Arquivo Padrão) onde os usuarios cadastram movimentações. A planilha contem varios tipos de validação de dados.

O problema é que se o usuario colar valores de outras planilhas (Arquivo X, Y, Z) na planilha padrão, esta aceita os valores sem estarem de acordo com a validação de dados definida. Não posso simplesmente bloquear a opção de colar pois existem muitas movimentações e não tem como ser feitas uma a uma, porem gostaria de verificar se existe a apção de colar obedecendo a validação de dados ou acusar erro se alguma celula não estiver de acordo com a validação.

Peço desculpas pelo extenso questionamento.

Desde já agradeço pela ajuda.

Abraço!

Answer 1

Francimar,

Aquela última rotina que mandei não bloqueou tudo na coluna A? Ou seja, não dá pro usuário colocar um texto ali que tenha uma letra minúscula que seja, mesmo usando Colar Especial Valores.

Cara, testei n vezes e funcionou. Será que não percebi algo?

Quanto a FC, acho melhor vc abrir outro tópico mesmo, pq este aqui ficou muito grande e ninguém vai olhar e ajudar

abraços,

M.

Answer 2

Ola Francimar, 

"Se cola o valor de uma celula em varias da ERRO e se colar especial valores de varias celulas tambem da erro... Será que escrevi o codigo errado?" O que acontece é que quando colas a celula, mesmo usando colar especial, validaçao, a origem é mudada, ou seja ele vai readaptar a origem. para que isso não aconteça, na funçao da validaçao de dados voce tem que bloquear a origem, ou seja, se por exemplo tem uma validaçao de A2>B2 e colar na celula A4 ele vai alterar para A4>B4.. Para que isso nao aconteca vc tem que bloquear a origem, ou seja, no endereço da condiçao que tem na validaçao vc tem que por $(antes da coluna) e$ (antes do nr da linha) .

Poste por favor aqui a condiçao que fez na validaçao, para eu poder tentar ajudar melhor.

Cumprimentos,

Jimmy C.

Answer 3

Francimar,

Imagine que vc criou validação de dados para as colunas A, B e C (é um exemplo)

Ponha isto no código da planilha onde os usuários entram com dados

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim VT1 As Long, VT2 As Long, VT3 As Long

    'Verifica se a Entrada é válida

    On Error Resume Next

    VT1 = Range("A:A").Validation.Type

    VT2 = Range("B:B").Validation.Type

    VT3 = Range("C:C").Validation.Type

    If Err.Number <> 0 Then

        Application.Undo

        MsgBox "Operação cancelada" & vbCrLf _

         & "Violação de regras de validação de Dados", vbCritical

         Exit Sub

    End If

    On Error GoTo 0            

End Sub

O que vai acontecer caso o usário cole algo que fira a as regras de validação?

Vai receber uma mensagem de erro e a operação será cancelada através do Application.Undo

Tenho exatamente isso numa planilha minha e funciona bem.

Vê se dá certo pra você.

Espero que ajude

M.

Answer 4

Francimar,

Já entendi o novo problema.

Experimente esta

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim VT1 As Long, cel As Range

    If Not Intersect(Range("A:A"), Target) Is Nothing Then

        On Error Resume Next

        VT1 = Range("A:A").Validation.Type

        If Err.Number <> 0 Then

            Application.Undo

            MsgBox "Operação cancelada" & vbCrLf _

            & "Violação de regras de validação de Dados", vbCritical

            Exit Sub

        End If

       Application.EnableEvents = False

       For Each cel In Target

            If cel.Value <> UCase(cel.Value) Then

                cel.Value = ""

            End If

        Next cel

        Application.EnableEvents = True

    End If

End Sub

Agora acho que ficou redondinho. Se o usuário colar valores várias células só as que estiverem corretas (Maiúsculas) serão aceitas. Vê aí.

M.

Answer 5

Bom dia Francimar,

Na celula de origem bloqueia com $ a funcao da validaçao. Exmplo $A$2>$B$2.

Epero ter ajudado,

Abraço,

Jimmy C.

Answer 6

Francimar,

O código que faz um Ctrl+z é aquele que eu pus na minha última resposta.

 If Target.Value <> UCase(Target) Then

            MsgBox "Erro"

            Application.Undo

            Exit Sub

End If  

O Application.Undo é igualzinho ao Ctrl+z. Apaga tudo que o usuário fez.

Valeu Francimar!!!

abraço,

M.

Answer 7

Ola Francimar,

Voce tem uma forma de fazer isso, faz copiar (de uma celula que tenha a validaçao correcta) e depois selecionas as 20000 celulas(nota que podes usar o ctrl +  click sobre celulas em varios sitios diferentes da planilha) depois fazes colar ---->especial----> validaçao.

Espero ter ajudado,

Abraço,

Jimmy C.

Answer 8

Francimar,

Vc tem razão. Com Colar Especial valores passa pelo crivo da minha rotina, porque colar só valores não altera a VD da célula.

Vejo duas possíveis soluções:

1. Repetir na rotina, além do VT1 = Target.Validation.Type, o critério de VD que vc usou na planilha colocando um IF e testando se todas as letras são maiúsculas.

Talvez algo como

 If Target.Value <> UCase(Target) Then

            MsgBox "Erro"

            Application.Undo

            Exit Sub

End If  

Isto dentro do primeiro IF (testei e funcionou legal)

Talvez até essa regra sozinha seja suficiente para o seu caso.

2. Usar Formatação Condicional

Selecionar toda coluna A, Formatação Condicional, Nova Regra, Usar uma fórmula, e inserir esta fórmula

=EXATO(A1;MAIÚSCULA(A1))=FALSO

botão Formatar e escolher preenchimento vermelho.

Ok, Ok

Aí o usário até consegue entrar com colar valores, mas vai tomar um susto quando vir a célula toda vermelha.

Aliás, eu quase sempre prefiro usar CF em vez de VD, pois deixa o usuário entrar mas o alerta para o erro.

abraços,

M.

Answer 9

Francimar,

Acho que já entendi. O usuário só pode entrar com letras maiúsculas na coluna A. Usei a sua fórmula e a minha rotina acima (a segunda que mandei) e funcionou nota 10!

Só consegue entrar com maiusculas e quando tenta copia e cola a minha rotina impede. Para mim deu tudo certo. Vê se não tem alguma sujeira na coluna A (alguma entrada errada feita anteriormente) pois a rotina verifica TODA a coluna A para ver se os critérios de validação estão intactos em TODAS as células da coluna. Pode-se mudar para verificar apenas a célula que está sendo alterada com

 VT1 = Target.Validation.Type

em vez

VT1 - Range("A:A").Validation.Type

Talvez assim fique melhor

M.

Answer 10

Francimar,

Explica melhor o que vc está tentando fazer com a fórmula  =EXATO( A17; MAIÚSCULA(A17))como validação para a coluna A.

Isto forçaria ou impediria o usuário de entrar o que? Tô meio confuso...

Quanto a outras colunas estarem produzindo a msg de erro: vc colocou

 If Not Intersect(Range("A:A"), Target) Is Nothing Then

Este IF faz com que só as entradas na coluna A sejam consideradas

Entradas em outras colunas não poderiam gerar qq msg de erro.

Vê aí e me diz.

M.

Answer 11

Francimar,

Este exemplo fica mais fácil de entender. Imagune que eu criei uma lista em X1: X3, por exemplo, com 3 valores: 1, 2 e 3. Selecionei a coluna A inteira e em Validação de Dados escolhi Lista e selecionei a lista que eu criei em X1:X3.

Agora qualquer entrada diferente de 1, 2 ou 3 será impedida, a menos que o usuário escreva, por exemplo, 5 em alguma célula e Copie e Cole em qq célula da coluna A, Isto faz com que a VD não funcione, e, pior, apaga a VD anteriormente criada, né mesmo?

Para evitar issto, coloca-se o seguinte código no evento Worksheet_Change da planilha

Private Sub Worksheet_Change(ByVal Target As Range)

     Dim VT1 As Long

    'Verifica se a entrada foi na coluna A

    If Not Intersect(Range("A:A"), Target) Is Nothing Then

        'Verifica se a Entrada é válida

        On Error Resume Next

        VT1 = Range("A:A").Validation.Type

        If Err.Number <> 0 Then

            'Desfaz a operação

            Application.Undo

            'Alerta o usuário

            MsgBox "Operação cancelada" & vbCrLf _

            & "Violação de regras de validação de Dados", vbCritical

            'Sai da Rotina

            Exit Sub

        End If

    End If

End Sub

Esta rotina faz o seguinte:

 1) cria uma variável VT1 de tipo Long porque os critérios de validação, internamente, são representados por números 

2. Os comentários na rotina esclarecem o que cada parte faz

Em resumo, se não houver erro não faz nada, e qdo identifica o erro, porque a variável VT1 perdeu a validação pelo copia e cola, desfaz a operação (recuperando a validação da célula afetada), dá uma mensagem de erro, e qdo o usuário clica ok no quadro de mensagem sai da rotina.

Espero ter sido claro.

É fundamental primeiro limpar todos os erros da Coluna A anteriormente entrados.

Para isso, no meu exemplo, seleciono toda a coluna A e em Dados>Validação de Dados escolho Circular Dados Inválidos. E aí corrijo os dados errados. Vc deve fazer isso na área (range) que vc usou a VD.

abraços,

M.

Answer 12

Francimar,

Não esqueça de depois de inserir a macro acima, salvar o arquivo como .xlsm e habilitar as macros quando abri-lo.

M.

Answer 13

Boa tarde M..

Estava testando a solução que apresentou e esta dando a menssagem de erro (MsgBox) em todo tipo de cola e proprio preenchimento...

Eu não entendi como é feita a verificação, se puder me explicar eu agradeço.

Obrigado pela resposta, esta sendo de grande ajuda.

Abraço.