1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| 'Equivalent de la formule:
'=SI(ESTERREUR(ET(GAUCHE(A5;3)="CC-";CNUM(STXT(A5;4;NBCAR(A5)-3))<53;
'CNUM(STXT(A5;4;NBCAR(A5)-3))>0;NBCAR(A5)<6));FAUX;ET(GAUCHE(A5;3)="CC-";
'CNUM(STXT(A5;4;NBCAR(A5)-3))<53;CNUM(STXT(A5;4;NBCAR(A5)-3))>0;NBCAR(A5)<6))
With Range("A5").Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=IF(ISERROR(AND(LEFT(A5,3)=""CC-"",VALUE(MID(A5,4,LEN(A5)-3))<53," & _
"VALUE(MID(A5,4,LEN(A5)-3))>0,LEN(A5)<6)),FALSE,AND(LEFT(A5,3)=""CC-"",VALUE" & _
"(MID(A5,4,LEN(A5)-3))<53,VALUE(MID(A5,4,LEN(A5)-3))>0,LEN(A5)<6))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Format CC-X ou CC-XX"
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Erreur de format "
.ShowInput = False
.ShowError = True
End With |
Partager