Re : Obliger format standard avec point et non virgule
voici mon code pour l'instant. Il oblige la majuscule partout et le code postal canadien dans le bon format.
Il me reste juste à mettre à la colonne 11 une obligation de . sans espace donc en standard : 1029.98
-----------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rg As Range
If Target.Count = 1 Then
Set Rg = Intersect(Target, Columns(20))
Application.EnableEvents = False
If Not Rg Is Nothing Then
For Each c In Rg
c.Value = UCase(Application.Trim(c))
If c.Value Like "[A-Z][0-9][A-Z] [0-9][A-Z][0-9]" Or _
c.Value Like "[A-Z][0-9][A-Z][0-9][A-Z][0-9*]" Then
c.Value = Left(c, 3) & " " & Right(c, 3)
c.Interior.ColorIndex = xlNone
c.Font.ColorIndex = xlAutomatic
ElseIf c.Value <> "" Then
MsgBox "la saisie du code postal est inexacte"
c.Interior.ColorIndex = 3
c.Font.ColorIndex = 2
Else
c.Interior.ColorIndex = xlNone
c.Font.ColorIndex = xlAutomatic
End If
Next
End If
Application.EnableEvents = True
'part2
If Target.Row = 1 Then Exit Sub
If Not IsNumeric(Target) Then
codeA = "ÉÈÊËÔéèêëàçùôûïî"
codeB = "EEEEOeeeeacuouii"
temp = Target
For i = 1 To Len(Target)
p = InStr(codeA, Mid(Target, i, 1))
If p > 0 Then Mid(temp, i, 1) = Mid(codeB, p, 1)
Next
Application.EnableEvents = False
Target = UCase(temp)
Else:
Application.EnableEvents = False
End If
Application.EnableEvents = True
End If
End Sub
--------------------------------------------------------