Limit characters and type of characters

Question

Limit characters and type of characters

Navigation

#1 by (1 votes)

2

I need some help.

I have a worksheet like this:

AndIwanttolimitamountofcharactersandtype

InthesegmentcolumnIwanttolimititto6charactersifitislessthan6ormorethan6.Returnsthemsg"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 the msg "Character limit not reached"

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

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

I'm not sure how to select the column and put the macro.

'If there are no 4 numbers' If IntSize < > 4 Then MsgBox "Unreached character limit", vbCritical Application.EnableEvents = False Rng.ClearContents Application.EnableEvents = True If anyone can help, I already thank

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

macro vba excel-vba

asked by anonymous 09.11.2017 / 17:04

1 answer

Function toggle menu does not work with data-attributes Error: Multiple controller types were found that match the URL

score 1 · Accepted Answer

Test data

Using the following test data:

EnterEventCode

UsetheWorksheet_Changeevent,whichmustbeplacedinsidetheworksheetwherethedatais(MARKET).Forexample,inmycaseitwasinSheet1:

Code

Thecodeisfiredeverytimetheworksheethasanychanges.

OptionExplicitPrivateSubWorksheet_Change(ByValTargetAsRange)OnErrorGoToCleanExitApplication.EnableEvents=FalseDimiAsLong'<--LinhaselecionadaDimNumeroCaractAsString,anomesdiaAsStringDimanoAsLong,mesAsLong,diaAsLong,DiasNoMesAsLongi=Target.Column'LimitaonúmerodecaracteresnacolunaBIfi=2ThenNumeroCaract=Len(Target.Value)IfNumeroCaract<>6ThenMsgBox"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 Num

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

CheckData

ThiscodeExtractseachpartoftheString(year,monthandday)andchecksiftheyearisnotlessthan1orifitisgreaterthanthecurrentyear(ifyouworkwiththefuturechangethispart).Ifthedayislessthan1orifitisgreaterthanthedaysthatmonthhas.Ifthemonthislessthan1orgreaterthan12.

CheckswhetheritisNumber

Thiscodeusesthe IsNumeric function to check if it is number.

Verify that 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.

Please note: If none of the above conditions are met, nothing happens. This code only creates an Error message.