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

Neste artigo é demonstrado como colar em uma planilha obedecendo a validação de dados no Excel.

Veja mais em: https://www.guiadoexcel.com.br/vba/

Se você tem uma planilha com validação de dados o usuário irá digitar os dados corretamente, mas se ele colar os dados você terá por padrão um problema no Excel, pois os dados não são validados neste momento, ignorando totalmente as configurações de bloqueio, e pior, matando toda essa configuração caso cole diretamente, sem colar valores.

Neste artigo demonstramos como solucionar o problema. para isso siga o artigo, o vídeo ou o arquivo para download.

Material gratuito

Planilha de Controle Financeiro no Excel

Tenha um controle bonito e completo do seu financeiro.

Categorias e subcategorias já configuradas
Planilhas separadas por tabela (configuração, lançamentos, resumo)
Personalize com suas próprias categorias e ícones

Preparando a planilha

A primeira parte para a preparação da planilha é selecionar a área aonde você tem a validação de dados e clicar com o botão direito desmarcando a opção Bloqueadas na guia Proteção.
Clique na guia Revisão e clique no botão Proteger Planilha. Deixe marcado apenas as duas opções "Selecionar células bloqueadas" e "Selecionar células desbloqueadas"
Estas opções são importantes para evitar que sejam excluídas as validações ao colar os dados.

Com esta correção nós reduzimos os problemas, mas ainda os dados incorretos podem ser colados. Siga a próxima etapa para bloquear que sejam colados dados incorretos.

Bloqueando colar dados incorretos na validação

Agora é necessário que seja incluso um código VBA na planilha, este código evitará que sejam colados dados incorretos na área com validação de dados.

Clique na guia Desenvolvedor e no botão Visual Basic ou pressione ALT+F11
Clique na planilha aonde há a validação de dados na árvore á esquerda.
Clique duas vezes na planilha e cole os valores no editor de código

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    
    If Not Intersect(Target, Me.Range("C9:C1048576")) Is Nothing Then
        For Each cell In Target
            If Not cell.Validation.Value Then
                MsgBox "Há um problema de dado inválido: " & cell.Value
                
                Application.Undo
                Exit Sub
            End If
        Next
    End If
End Sub

4. Altere o endereço no Range em C9:C1048576 conforme o range que você deseja que tenha os dados protegidos.

Veja abaixo o resultado final.