Impulsionando a entrada de formato em uma célula

Question

Eu preciso da minha planilha para permitir que os usuários insiram somente no formato hh.mm/hh.mm para o padrão de trabalho, por exemplo. alguém trabalha 12,5 horas em uma semana de 37 horas só seria permitida a entrada como 12,50 / 37,00

    

Answer 1

No editor de VBA, crie um novo Módulo na sua pasta de trabalho (você precisará salvá-lo como .xls ou .xlsm , não .xlsx ) e cole o código assim:

Function RegEx(Pattern As String, TextToSearch As String) As String 'vv
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = False
        .Pattern = Pattern
    End With

    Set REMatches = RE.Execute(TextToSearch)
    If REMatches.Count > 0 Then
        RegEx = REMatches(0)
    Else
        RegEx = vbNullString
    End If
End Function '^^

Public Function IsGood(stir As String)
    If RegEx("[0-9][0-9]\.[0-9][0-9]/[0-9][0-9]\.[0-9][0-9]", stir) = stir Then
        IsGood = "GOOD"
    Else
        IsGood = "BAD"
    End If
End Function

A primeira função, RegEx , realiza uma correspondência de expressão regular. A segunda função, IsGood , retorna a string GOOD se a string de saída corresponder à string de entrada (ou seja, a string inteira corresponde exatamente à regex) e BAD caso contrário.

Agora, em uma coluna sobressalente em algum lugar (pode ser uma coluna oculta) em sua planilha, escreva uma fórmula como:

=IsGood(A1) (usando âncoras conforme apropriado se você precisar fazer isso para várias linhas)

Em seguida, vá para Dados - > Validação de dados - > Validação de dados ... na faixa de opções, com as células que contêm os dados para testar selecionados na planilha .

Altere a caixa suspensa "Permitir:" para "Personalizar" e a "Fórmula", por exemplo:

=B1="GOOD"

Substitua "B1" pela referência à primeira célula que contém a =IsGood formula - não das células que contêm seus dados de entrada.

Agora, para testar seus resultados:

• Insira algo como "olá" em uma célula sujeita a validação de dados, e você receberá uma mensagem de erro e não poderá fazer a edição.
• Insira algo como "12.34 / 56.78" e funcionará bem, como normalmente no Excel.
• Insira algo como "12.34,56.78" e você receberá o erro de validação de dados porque a vírgula não corresponde à expressão regular.

Você pode ajustar o regex no meu código para ser mais flexível se aceitar valores como "12" em vez de "12.00", mas isso dependerá de suas necessidades exatas e de sua capacidade de compreender expressões regulares.

Basicamente, [0-9][0-9]\.[0-9][0-9]/[0-9][0-9]\.[0-9][0-9] é uma expressão regular que diz "coincide com um número de dois dígitos seguido por um período, depois outro número de dois dígitos, depois uma barra, depois outro número de dois dígitos e um ponto. outro número de 2 dígitos ". Isso é bastante rigoroso:

• Quaisquer espaços não serão compatíveis com
• Se qualquer um dos números contiver 0, 1 ou 3 ou mais dígitos, não será possível corresponder. Por exemplo, 12.375/37.00 falharia devido ao dígito extra, 5 .
• O uso de uma barra invertida \ em vez de uma barra / não seria compatível. Você pode usar o caractere de alternação | na regex para indicar que uma das duas possibilidades é aceitável e, dessa forma, você pode permitir OU uma barra invertida OU uma barra invertida.
• As possibilidades continuam. Você pode ser mais flexível e permitir qualquer número de dígitos usando quantificadores. Não vou cobrir todas as possibilidades de expressões regulares nesta resposta.