Forcer format de saisie textbox

Option Explicit
Private Sub TextBox1_Change()
On Error Resume Next
    Dim Valeur As Byte
    TextBox1.MaxLength = 8 'nb caractères maxi autorisé dans le textbox
    Valeur = Len(TextBox1)
    If Valeur < 3 Or Valeur > 6 Then
    If Not IsNumeric(Right(TextBox1, 1)) Then
        MsgBox "Le caractere saisi n'est pas valide" 'non numérique
        TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
    End If
    ElseIf IsNumeric(TextBox1.Value) Then
    MsgBox "Le caractere saisi n'est pas valide" 'numérique
    End If
End Sub

Private Sub TextBox5_Change()
Dim Formatage As String, LenT As Integer
Const CH As String = "111-AAA-11"
Const COMP As String = "###[-][a-zA-Z][a-zA-Z][a-zA-Z][-]##"

    LenT = Len(TextBox5.Value)
    If LenT <= Len(CH) Then
        If LenT > 0 Then
            Formatage = TextBox5.Value & Right(CH, Len(CH) - LenT)
            If Not Formatage Like COMP Then TextBox5.Value = Left(TextBox5.Value, LenT - 1)
        End If
    Else
        TextBox5.Value = Left(TextBox5.Value, LenT - 1)
    End If
End Sub