Como limitar Entrada em um intervalo de células para ser numérico (inteiro #) comprimento máximo 9 caracteres, sem zeros à esquerda e sem duplicatas?

Question

Como limitar Entrada em um intervalo de células para ser numérico (inteiro #) comprimento máximo 9 caracteres, sem zeros à esquerda e sem duplicatas?

Eu também quero limitar outro intervalo de células ao máximo de 24 alfa e "-" somente

    

Answer 1

A primeira parte da sua pergunta pode ser realizada com validação de dados e um formato personalizado. Para fazer isso, primeiro selecione o intervalo inteiro a ser validado e clique no botão Validação de dados na faixa de opções Dados. Em seguida, para definir o formato personalizado, na guia Configurações da caixa de diálogo Validação de dados, altere a lista suspensa Permitir para "Personalizado" e cole ou digite a fórmula incluindo a porcentagem principal = na caixa Fórmula:

=AND(A1 > 0, A1 < 1000000000, A1 = INT(A1), COUNTIF(A:A, A1) <= 1)

• A fórmula AND retornará verdadeiro se todos os argumentos forem verdadeiros.
• A1 > 0 valida que o número é positivo.
• A1 < 1000000000 valida que o número tem no máximo 9 caracteres.
• A1 = INT(A1) valida que o número não é um decimal.
• COUNTIF(A:A, A1) <= 1 valida que o intervalo contém apenas 1 instância do número.

Não há necessidade de verificar se não há zeros à esquerda, a menos que o intervalo esteja formatado como texto, pois o Excel remove automaticamente os zeros à esquerda dos números.

Esta fórmula pressupõe que seu intervalo de destino seja a coluna A. Altere todas as instâncias de A1 na fórmula para a primeira célula no intervalo de destino e o $A:$A para o intervalo inteiro. Certifique-se de usar o formulário de referência relativo ao se referir à primeira célula e ao formulário de referência absoluta ao se referir ao intervalo inteiro (por exemplo, B1 para a primeira célula e $B$1:$B$20 para toda a faixa).

Os benefícios de usar a validação de dados são que não há necessidade de usar macros e você pode especificar uma mensagem de entrada personalizada e uma mensagem de erro personalizada diretamente na caixa de diálogo Validação de dados.

A segunda parte da sua pergunta não pode ser facilmente realizada sem algum VBA. Se você estiver trabalhando com uma tabela e aplicando a validação de dados a uma coluna, isso pode ser feito facilmente usando uma coluna oculta e uma função personalizada semelhante à seguinte:

Function LimitAlpha24(str As String) As Boolean
    Dim rx As Object
    Set rx = CreateObject("VBScript.RegExp")
    rx.Pattern = "^[A-Za-z-]{0,24}$"
    LimitAlpha24 = rx.Test(str)
End Function

    

Answer 2

Coloque a seguinte macro de evento na área de código da planilha:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, v As Variant, L As Long, i As Long
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    Set A = Range("A1:A10")
    If Intersect(Target, A) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    v = Target.Text
    L = Len(v)

    If L > 9 Then
        MsgBox "input too long"
        GoTo errOut
    End If

    For i = 1 To L
        If Mid(v, i, 1) Like "[0-9]" Then
        Else
            MsgBox "bad character"
            GoTo errOut
        End If
    Next i

    If Left(v, 1) = "0" Then
        MsgBox "leading zero"
        GoTo errOut
    End If

    If wf.CountIf(A, v) > 1 Then
        MsgBox "duplicate value"
        GoTo errOut
    End If
    Application.EnableEvents = True
    Exit Sub

errOut:
    Target.Clear
    Target.Select
    Application.EnableEvents = True
End Sub

O intervalo de células assumido é A1: A10

Por ser um código de planilha, é muito fácil de instalar e usar automaticamente:

1. clique com o botão direito do mouse no nome da guia próximo à parte inferior da janela do Excel
2. selecione Exibir código - isso abre uma janela do VBE
3. cole o material e feche a janela do VBE

Se você tiver alguma dúvida, tente primeiro em uma planilha de teste.

Se você salvar a pasta de trabalho, a macro será salva com ela. Se você estiver usando uma versão do Excel posterior a 2003, deverá salvar o arquivo como .xlsm em vez de .xlsx

Para remover a macro:

1. abrir as janelas do VBE como acima
2. limpe o código
3. feche a janela do VBE

Para saber mais sobre macros em geral, consulte:

link

e

link

Para saber mais sobre macros de eventos (código da planilha), consulte:

link

As macros devem estar ativadas para que isso funcione!