Limit characters and character type

Question

Limit characters and character type

I need help.

I have a spreadsheet like this:

And I want to limit amount of characters and type

In the segment column I want to limit by 6 characters, if it is less than 6 or more than 6. Returns msg "character limit not reached"

In the date column I want to limit the date type to 8 characters, if it is less than 8 or more than 8. Returns msg "character limit not reached"

And na column number I want to limit the type number to 4 characters, if it is less than 4 or more than 4. Returns msg "character limit not reached"

I started writing something, but I'm not seeing where I'm going wrong. And honestly for a long time I do not write VBA code

I don't remember how to select the column and place the macro.

'if there are no 4 numbers' If Intsize 4 Then MsgBox "character limit not reached", vbCritical Application.EnableEvents = False Rng.ClearContents Application.EnableEvents = True If anyone can help, I already appreciate

Código está assim:
Sub LimitarCaractere()

Dim TRACKER As Workbook '<-- Tracker
Dim MARKET As Worksheet '<-- Aba
Dim i As Long           '<-- Linha selecionada
Dim SEGM As String      '<-- Segment
Dim FLT As Integer     '<-- Flight
Dim Ddate As Date     '<-- Date

Set TRACKER = ActiveWorkbook
Set MARKET = TRACKER.ActiveSheet
i = ActiveCell.Row

'Limita o número de cacteres na célula
If i > 6 Then
     ActiveCell = Left(ActiveCell, 6)
    MsgBox "Limite de caracteres Ultrapassado na Célula: " _
    & ActiveCell
End If
End Sub

Thank you

2

vba excel-vba macro

Author: Maniero, 2017-11-09

Source

1 answers

score 1 · Accepted Answer

Test Data

Using the following data for testing:

Insert event Code

Use the event Worksheet_Change, in which it should be placed within the spreadsheet in which the data is located (MARKET). For example, in my case it was in Plan1:

Code

The code is triggered every time the worksheet has any changes.

    Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo CleanExit
    Application.EnableEvents = False
    Dim i As Long                                '<-- Linha selecionada
    Dim NumeroCaract As String, anomesdia As String
    Dim ano As Long, mes As Long, dia As Long, DiasNoMes As Long

    i = Target.Column
    'Limita o número de caracteres na coluna B
    If i = 2 Then
        NumeroCaract = Len(Target.Value)
        If NumeroCaract <> 6 Then
            MsgBox "Limite de caracteres Ultrapassado na Célula: " & Target.Address
        End If
    End If

    'Limita o número de caracteres na coluna C e verifica data
    If i = 3 Then
        'Verifica Num Caract
        NumeroCaract = Len(Target.Value)
        If NumeroCaract <> 8 Then
            MsgBox "Limite de caracteres Ultrapassado na Célula: " & Target.Address
        End If
        'Verifica Data
        anomesdia = Target.Value
        ano = CLng(Left(anomesdia, 4))
        mes = CLng(Left(Mid(anomesdia, 5), 2))
        dia = CLng(Right(anomesdia, 2))
        DiasNoMes = Day(DateSerial(ano, mes + 1, 0))

        If mes < 1 Or mes > 12 Or dia < 1 Or dia > DiasNoMes Or ano < 1 Or ano > Year(Now) Then 'Checa se não é data
            MsgBox "Não é data na Célula: " & Target.Address
        End If
    End If

    'Limita o número de caracteres na coluna F e verifica se é número
    If i = 6 Then
        'Verifica Num Caract
        NumeroCaract = Len(Target.Value)
        If NumeroCaract <> 4 Then
            MsgBox "Limite de caracteres Ultrapassado na Célula: " & Target.Address
        End If
        'Verifica Se é Número

        If Not IsNumeric(Target) Then 'Checa se é data
            MsgBox "Não é número na Célula: " & Target.Address
        End If
    End If

CleanExit:
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Explanation

Check Num Character

This code used the LEN function to check the number of characters in the String.

Check Date

This code extracts each part of the String (year, month and day) and checks if the year is not less than 1 or if it is greater than the current year (if you work with the future to change this part). If the day is less than 1 or if it is greater than the days that that month has. If the month it is less than 1 or greater than 12.

checks if it is number

This code uses the isnumeric function to verify that it is a number.

Checks if the changed cell is in the desired column

If i = 2 Then it is the conditional that checks if it is the target column, counting by index. Where 1 is A, 2 is B and so on.

Note that: if none of the previous conditions are reached, nothing happens. This code only creates an error message.