Limit characters and character type

Question

I need some help.

I have a spreadsheet this way:

And I want to limit the amount of characters and the type

In the segment column I want to limit it to 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 to msg "Character limit not reached"

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

I started writing something, but I’m not seeing where I’m going wrong. And honestly I haven’t written VBA code for a long time

I can’t remember how to select the column and put 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’d appreciate it

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

Answer 1

Test data

Using the following test data:

Insert Event code

Use the event Worksheet_Change, where the data is to be placed within the spreadsheet (MARKET). For example, in my case it was in Planilha1:

Code

The code is triggered every time the spreadsheet 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

• Checks On One Caract

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

• Verifies Data

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 larger than the current year (if working with the future change this part). If the day is less than 1 or if it is longer than the days that month has. If the month is less than 1 or more than 12.

• Check If It’s Number

This code uses the Isnumeric function to check if it’s number.

• Checks if the changed cell is in the desired column

  If i = 2 Then 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: If none of the above conditions are met, nothing occurs. This code only creates an Error message.