Postado por: @mnascimento1

↑

Porém na coluna D funcionou perfeitamente... já na coluna E dá a msg de erro em anexo e aceita salvar qualquer valor digitado.

Olá, @mnascimento1

Embora o seu código possa ser melhorado, aparentemente o erro citado não tem relação com o código e sim com a Validação de Dados existente naquela coluna.

Se você não conseguir resolver, então disponibilize o seu arquivo Excel com a macro, imagens não servem.

dica - ao postar códigos utilize o ícone Código (<>) disponível na barra de ferramentas da caixa de resposta.

Bom dia @osvaldomp !!!!

Não tenho duvidas que meu código possa ser melhorado... e muito... pq como disse... é minha primeira vez... rsrsrss

Então meu caro... fiquei ontem até tarde tentando entender este código, o erro e o por que na Coluna F ele não funciona. Não faz o TRIM não faz a verificação de formato de inicio do código... ou seja nada...  
Vou te enviar o arquivo... e o código da maneira que vc falou... quem sabe vc consegue me ajudar

Att,

Marcelo.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    Dim valor As String
    
    ' Desativa eventos para evitar loops infinitos
    Application.EnableEvents = False
    
    ' Verifica se a alteração ocorreu no intervalo D5:D500
    If Not Intersect(Target, Me.Range("D5:D500")) Is Nothing Then
        For Each cel In Target
            If Not IsEmpty(cel.Value) Then
                ' Remove espaços extras no início e no fim
                valor = Trim(cel.Value)
                
                ' Remove espaços internos extras
                valor = Replace(valor, " ", "")
                
                ' Mantém apenas os primeiros 9 dígitos
                If Len(valor) > 9 Then
                    valor = Left(valor, 9)
                End If
                
                ' Verifica se o valor tem exatamente 9 caracteres numéricos
                If Len(valor) <> 9 Or Not IsNumeric(valor) Then
                    MsgBox "Valor inválido!", vbExclamation
                    cel.ClearContents ' Limpa o conteúdo da célula
                Else
                    ' Atualiza a célula com o valor formatado
                    cel.Value = valor
                End If
            End If
        Next cel
    End If

    ' Verifica se a alteração ocorreu no intervalo E5:E500
    If Not Intersect(Target, Me.Range("E5:E500")) Is Nothing Then
        For Each cel In Target
            If Not IsEmpty(cel.Value) Then
                ' Remove espaços extras no início e no fim
                valor = Trim(cel.Value)
                
                ' Remove espaços internos extras
                valor = Replace(valor, " ", "")
                
                ' Verifica se o valor tem exatamente 20 caracteres, começa com "1401-" e tem 15 caracteres numéricos após
                If Len(valor) <> 20 Or Left(valor, 5) <> "1401-" Or Not IsNumeric(Mid(valor, 6, 15)) Then
                    MsgBox "Valor inválido!", vbExclamation
                    cel.ClearContents ' Limpa o conteúdo da célula
                End If
            End If
        Next cel
    End If
    
    ' Reativa eventos
    Application.EnableEvents = True
End Sub

1. o erro ocorre porque ao inserir um conteúdo em qualquer célula que tenha Validação de Dados, a Validação é disparada antes da macro.

Para corrigir basta remover a Validação.

2. o conteúdo da célula não é corrigido pela macro porque falta inserir na célula o conteúdo da variável valor.

Para corrigir, acrescente a linha conforme abaixo.

If Len(valor) <> 20 Or Left(valor, 5) <> "1401-" Or Not IsNumeric(Mid(valor, 6, 15)) Then
MsgBox "Valor inválido!", vbExclamation
cel.ClearContents ' Limpa o conteúdo da célula
Else: cel.Value = valor 'acrescente esta linha
End If

Se você tiver interesse eu posso simplificar/melhorar o seu código.

@osvaldomp 

Entendi!

O que me causa estranheza e que a validação na célula D, apesar de ser simples, funciona... mas enfim... coisas que a gente vai aprendendo com o tempo.

Quanto a sua ajuda, como seria?
Você faria para mim? Teria algum custo??? Se sim, qual o valor?

Pergunto por que estou fazendo por conta própria e não tenho verba para isso, e a empresa  não irá me reembolsar.

Postado por: @mnascimento1

↑

Teria algum custo?

Em troca da ajuda recebida aqui, ajude alguém que esteja precisando.

Experimente o código abaixo no lugar do existente.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
On Error GoTo fim
Application.EnableEvents = False

If Not Intersect(Target, Range("D5:D500")) Is Nothing Then
Target.Value = Replace(Target.Value, " ", "")
If Len(Target.Value) <> 9 Or Not IsNumeric(Target.Value) Then
MsgBox "Valor inválido!", vbExclamation
Target.Value = ""
End If
ElseIf Not Intersect(Target, Range("E5:E500")) Is Nothing Then
Target.Value = Replace(Target.Value, " ", "")
If Len(Target.Value) <> 20 Or Left(Target.Value, 5) <> "1401-" Or Not IsNumeric(Mid(Target.Value, 6, 15)) Then
MsgBox "Valor inválido!", vbExclamation
Target.Value = ""
End If
End If

fim:
Application.EnableEvents = True
End Sub

dicas:

1. para acessar a ajuda do VBA coloque o cursor sobre o comando e aperte F1

2. para executar o código no modo Depurar, marque um ponto de interrupção, insira algum conteúdo no intervalo de interesse e a partir daí execute o código via tecla F8. Ajuste o tamanho e a posição da janela do VBA assim você poderá ver na planilha o efeito de cada comando executado.