Textbox character counr before submit

[ipbr21054]

Evening,
I have a userform where TextBox2 needs to have 17 characters entered.
It must be 17 or show Msgbox.


I am not sure if the code should be on that Textbox so when i leave it i see the Msgbox Or when i press the command button to transfer userform values to worksheet.
I also paste into Textbox 2 should you need to know.
Below i have something which kind of works on the Textbox but as soon as i strart to type 1 letter the msgbox is shown.

What does you advise.
Basically if Textbox2 isnt 17 characters then no values should be transfered to worksheet

Private Sub TextBox2_Change()
If TextBox2.Value <> 17 Then
   MsgBox "VIN MUST BE 17 CHARACTERS", vbCritical, "VIN CHARACTER COUNT MESSAGE"
   TextBox2.Value = ""
Else
Exit Sub
    TextBox2 = UCase(TextBox2)
    End If
End Sub

Userform code.

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim X As Long
    Dim ControlsArr(1 To 8) As Variant
    
    
    For i = 1 To 8
        If i > 2 Then
            With Me.Controls("ComboBox" & i)
                If .ListIndex = -1 Then
                    MsgBox "YOU MUST COMPLETE ALL FIELDS", vbCritical, "MC LIST TRANSFER"
                    TextBox1.SetFocus
                    Exit Sub
                Else
                    ControlsArr(i) = .Value
                End If
            End With
        Else
            ControlsArr(i) = Me.Controls("TextBox" & i).Value
        End If
    Next i
        
        Application.ScreenUpdating = False
        
        With ThisWorkbook.Worksheets("MC LIST")
            .Range("A8").EntireRow.Insert Shift:=xlDown
            .Range("A8:I8").Borders.Weight = xlThin
            .Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
            

        End With
        Range("B8").Select
        Range("A8").Select
        ActiveWorkbook.Save

        Application.ScreenUpdating = True
        MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
        
       With ThisWorkbook.Worksheets("MC LIST")
      
       If .AutoFilterMode Then .AutoFilterMode = False
            X = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A7:I" & X).Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess
            .Range("B8").Select
            .Range("A8").Select
        End With
        
        Unload McListForm
End Sub

 

[Yongle]

1. Test this by creating a NEW userform containing only TextBox1 and Texbox2

2. Place this code in the userform code window

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Me.TextBox1.Text) < 17 Then Cancel = True
End Sub

Private Sub TextBox1_Change()
    Me.TextBox1.Value = Left(Me.TextBox1.Value, 17)
End Sub

3. Run the userform and
- enter abc into TextBox1, click on Textbox2 the code prevents user from leaving TextBox1
- keep adding characters def .... etc , click on Textbox2 the code prevents user from leaving TextBox1
- when 17 characters are in the textbox user is able to leave the TextBox1
- try entering 18 characters in TextBox1 the code prevents more than 17 characters being entered

4. If it does what you want, incorporate this method into your code

 

[ipbr21054]

Hi,
Yes it works but not being rude might be a bit extreme ?
Im thinking it might be best to only allow transfer to worksheet so check for 17 characters when command button pressed

 

[ipbr21054]

It should be simple enough by using the following but placing it in different locations im getting all kind of errors.

If TextBox2.Value <> 17 Then
   MsgBox "VIN MUST BE 17 CHARACTERS", vbCritical, "VIN CHARACTER COUNT MESSAGE"

 

[Yongle]

Yes it works but not being rude might be a bit extreme ?

From that response I can only infer that you have never been an accountant!

This is what you wrote in post#3

Im thinking it might be best to only allow transfer to worksheet so check for 17 characters when command button pressed

Perhaps this combination provides what you want

Private Sub CommandButton1_Click()
    If Len(Me.TextBox2.Value) = 17 Then
        'CODE TO UPDATE WORKSHEET VALUES GOES HERE
    Else
        MsgBox "TextBox2 needs 17 characters" & vbCr & "worksheet was not updated", vbExclamation, "OOPS!"
    End If
End Sub

Private Sub TextBox2_Change()
'prevent user entering more than 17 characters
    Me.TextBox2.Value = Left(Me.TextBox1.Value, 17)
End Sub

 

[ipbr21054]

Morning,

I have only used the large code as the small code wasnt needed.

Thanks

 

[Yongle]

glad you solved your problem
thanks for your feedback