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

Download de nota fiscal eletrônica XML nfe Excel capa
Download de XML de NFe Excel
4 de agosto de 2018
Gráfico comparativo de linhas capa
Gráfico comparativo de linhas – Exemplo Brasileirão 2018 1º turno
28 de agosto de 2018

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.

Preparando a planilha

  1. 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.Bloquear colar dados incorretos em validação de dados 1
  2. 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”Bloquear colar dados incorretos em validação de dados 2
  3. 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.

  1. Clique na guia Desenvolvedor e no botão Visual Basic ou pressione ALT+F11
  2. Clique na planilha aonde há a validação de dados na árvore á esquerda.Bloquear colar dados incorretos em validação de dados 3
  3. 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.

Bloquear colar dados incorretos em validação de dados

Abraço

Marcos Rieper

2 Comments

  1. MARKLAND disse:

    Olá! Marcos Ripper tudo bem?
    Primeiramente, gostaria de parabenizar pelo excelente trabalho que você faz no seu blog. Acompanho a todos conteúdo e é muito bom!!

    Meu caro, me ajude pelo amor de Deus, eu sou recém contratado em um almoxarifado e preciso otimizar uma planilha de estoque. Porém sou muito júnior em VBA se poder me ajudar ficarei muito grato. Minha situação é o seguinte, eu preciso de um código em VBA que me ajude a buscar “VALORES DUPLOS” em uma planilha com duas abas deferente: Plan8 e Plan3 na mesma Plan Principal.

    Plan8: Relatório
    Plan3: Analítico

    Eu tenho o código em VBA abaixo porem não está funcionando para essa planilha não entendo porque, mas com sua ajuda fica mais fácil. O código não foi eu quem fiz eu copiei de uma outra planilha existente que funciona muito bem, porém na minha não. Deixa me descrever melhor:

    PASSO 1: Planilha relatório:
    Eu tenho na Coluna “C”, linha 7 PLAN8 de relatório, um “CÓDIGO MESTRE” de estoque. Onde cada item cadastrado recebe um número a partir da compra.

    PASSO 2: Planilha dados:
    Na Coluna “B”, linha 2 PLAN3, eu tenho um analítico de compra com diversos “CÓDIGO MESTRE repetidos por datas de entrada e de compra.

    Eu gostaria de um código VBA que retorne um valor duplo das coluna “A”; “G”,”I” e “K” a partir do “CÓDIGO MESTRE” da coluna “B” Plan3 do tipo PROCV + CONCATENADO com “/”. Para minha planilha de Relatório Plan8.

    ————————————
    CÓDIGO:

    Option Explicit
    Function PROCVCONCAT(sProcura As String, vBD As Variant, vBDout As Variant, lngOffset As Long)
    ‘ (valor a ser procurado(“0″ para > 0), matriz de busca do valor, matriz de saida de retorno da linha NOME, coluna da matriz de saida BUSCA PROJETO E %)
    ‘Altere essa constante se quiser utilizar outro caractere como dígito separador.
    Const strSeparador As String = ” / ”
    Dim l As Long
    Dim lngTotal As Long
    Dim strTemp() As String
    Dim varTemp As Variant
    Dim varOut As Variant

    ‘Transformo o parâmetro de entrada (que pode ser uma matriz ou uma Range) para trabalhar
    ‘apenas com uma Variant:
    varTemp = CVar(vBD)
    varOut = CVar(vBDout)
    lngTotal = 0

    If sProcura = 0 Then
    For l = LBound(varTemp, 1) To UBound(varTemp, 1)
    If varTemp(l, 1) > sProcura Then ‘Formula de teste para o critério de procura.
    ‘Foi encontrada uma correspondência na primeira coluna do vetor de varTemp.
    If IsArrayEmpty(strTemp) Then
    Else
    If strTemp(lngTotal) = varOut(l, lngOffset) Then
    GoTo jump:
    End If
    End If
    lngTotal = lngTotal + 1
    ReDim Preserve strTemp(1 To lngTotal)
    strTemp(lngTotal) = varOut(l, lngOffset)
    jump:
    End If
    Next l
    Else
    For l = LBound(varTemp, 1) To UBound(varTemp, 1)
    If varTemp(l, 1) = sProcura Then ‘Formula de teste para o critério de procura.
    ‘Foi encontrada uma correspondência na primeira coluna do vetor de varTemp.
    lngTotal = lngTotal + 1
    ReDim Preserve strTemp(1 To lngTotal)
    strTemp(lngTotal) = varOut(l, lngOffset)
    End If
    Next l
    End If

    If IsArrayEmpty(strTemp) Then
    ‘Caso não seja encontrada nenhuma correspondência, a função retornará uma célula vazia.
    PROCVCONCAT = “-”
    Exit Function
    Else
    ‘Join concatena todas as correspondências encontradas do vetor strTemp:
    PROCVCONCAT = Join(strTemp, strSeparador)
    End If
    End Function

    Private Function IsArrayEmpty(v As Variant) As Boolean
    On Error Resume Next
    If LBound(v) 0 Then IsArrayEmpty = True
    End Function

  2. CELSO INOCENCIO disse:

    Marcos, bom dia

    Preciso de ajudar com uma planilha. Preciso copiar os dados de uma coluna, par outro sheet outra coluna.
    Porem, preciso copiar somentes os valores das celulas cuja a cor e amarela, fomando assm uma nova coluna somente com o que eu preciso.

    como fazer?

Deixe uma resposta

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

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

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.