Minimum value in textbox

[johnsonk]

Hi, can anybody help I am trying to set a minimum value when scanning a barcode in to a textbox to a minimum of 47 digits so if a barcode is scanned with less than 47 digits it will error but I can not get it to work. below is the vba I am using now which works fine for scanning but it excepts barcodes with less than 47 digits I have tried using if len(textbox2.value) <> 47 with now joy.

Private Sub textbox2_Change()
Dim erow As Long
If Len(TextBox2.Value) = 47 Then
Application.DisplayAlerts = False
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = TextBox1.Text
Cells(erow, 2) = TextBox2.Text
Cells(erow, 7) = TextBox3.Text
Cells(erow, 6) = TextBox4.Text
Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Now
Cells(erow, 3).FormulaR1C1 = "=mid(rc[-1],4,4)"
Cells(erow, 4).FormulaR1C1 = "=mid(rc[-2],8,4)"
Cells(erow, 5).FormulaR1C1 = "=mid(rc[-3],30,6)"
Range("A1").End(xlDown).Select
TextBox2.Text = ""
TextBox3.Text = ""
TextBox2.Visible = True
TextBox4.Text = ""
TextBox1.Visible = True
TextBox3.Visible = False
TextBox4.Visible = False
Label3.Visible = False
Label4.Visible = False
Label5.Visible = False
Label6.Visible = False
Application.DisplayAlerts = True
TextBox2.SetFocus
End If
End Sub

Regards

 

[dmt32]

hi,
maybe you are using the wrong event for what you are trying to do

try this & see if any help

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim erow As Long
    If Len(TextBox2.Value) = 47 Then
        
        erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Cells(erow, 1) = TextBox1.Text
        Cells(erow, 2) = TextBox2.Text
        Cells(erow, 7) = TextBox3.Text
        Cells(erow, 6) = TextBox4.Text
        Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Now
        Cells(erow, 3).FormulaR1C1 = "=mid(rc[-1],4,4)"
        Cells(erow, 4).FormulaR1C1 = "=mid(rc[-2],8,4)"
        Cells(erow, 5).FormulaR1C1 = "=mid(rc[-3],30,6)"
        
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox2.Visible = True
        TextBox4.Text = ""
        TextBox1.Visible = True
        TextBox3.Visible = False
        TextBox4.Visible = False
        Label3.Visible = False
        Label4.Visible = False
        Label5.Visible = False
        Label6.Visible = False
        
    Else
        MsgBox "Invalid Bar Code", 48, "Invalid Entry"
        Cancel = True
    End If
End Sub

Dave

 

[johnsonk]

Hi, I have tried your code and it does not transfer the data to the sheet when scanned, I also scanned a barcode with less than 47 digits and that did nothing either.

Regards

 

[dmt32]

did you delete the other code in the Change event?

Dave

 

[johnsonk]

Yes deleted all the change event coded and used yours..

 

[dmt32]

Yes deleted all the change event coded and used yours..

Code provided is your code just placed in another of the Controls event and If you enter number directly, solution works.

Whilst I have no experience of Barcode scanners being used with Excel in manner you require, it is my understanding that you can program most bar code scanners to have an "Enter" keystroke after every bar code that you scan – this should then activate the event. May be worth checking if your scanner is doing this?

Dave

 

[johnsonk]

Thank you I will look in to barcode scanner

 

[johnsonk]

I have checked the scanner and that is doing the enter keystroke and I have tried different do you have any other ideas ?

 

[dmt32]

I have checked the scanner and that is doing the enter keystroke and I have tried different do you have any other ideas ?

Assuming that the Control your scanner is placing data to is TextBox2 then I would have thought it would have worked but as stated, I personally have no experience of using barcode scanner with excel.

Hopefully, another here may be able to assist you

Dave

 

[johnsonk]

Ok Dave thank you for you time..