VBA - Userform Validate Text in TextBox?

[NessPJ]

Hi there,

I have a form where i have users input text in a textbox. Then as they input characters, the data is validated against a table to look up / validate some information.
I posted the code i use for this below.

Now it works...but as soon as the user inputs more than 3 digits i get an Error 1004 on the line "CurBarcodePartDest = Cells(WorksheetFunction.Match......"

How do i make it so the validation is checking if the first 2, 3 or 4 digits can actually be found using the Vlookup?
Because right now it either has to be correct data right away or if the data is false the validation will not longer continue running the rest of the code...

Sub TextBoxInvoer_Change()
    Public CurBarcodeSTR As String, CurAppIDType As String, CursAppID As Long, CurAppIDValidation As String, CurAppIDLength As Long, CurAppIDDataLength As Long, CurBarcodePart As String, CurBarcodePartDest As String, CurBarcodeCounterDest As String, CurBarcodeSTRLength As Long


'Text invoer opschonen voor barcode ontleding
Const SpecialCharacters As String = ".>,>`>~>/>\>!>@>#>$>%>^>&>*>(>)>{>[>]>}>?>;>=>-"     'modify as needed (">" is the divider)

Dim myString As String, newString As String, char As Variant

myString = TextBoxInvoer.Text
newString = myString

For Each char In Split(SpecialCharacters, ">")
    newString = Replace(newString, char, "")
Next

'Parameter Tabel initialiseren
Sheets("Parameters").Range("J4:J21").Value = 0
Sheets("Parameters").Range("K4:K21").ClearContents

'Start barcode ontleding
CurBarcodeSTR = Replace(newString, vbCr + vbLf, "")
CurBarcodeSTR = Replace(CurBarcodeSTR, " ", "")

'Invoer validatie
If (Len(CurBarcodeSTR) >= 0 And Len(CurBarcodeSTR) <= 3) And Application.IsNA(Application.VLookup(CurBarcodeSTR, Sheets("Parameters").Range("E4:K21"), 2, False)) Then GoTo Einde

CurAppID = Mid(CurBarcodeSTR, 1, 2)             'AppID om op te zoeken

If Application.IsNA(Application.VLookup(CurAppID, Sheets("Parameters").Range("E4:K21"), 2, False)) Then        'AppID valideren
    CurAppID = Mid(CurBarcodeSTR, 1, 4)
ElseIf Application.IsNA(Application.VLookup(CurAppID, Sheets("Parameters").Range("E4:K21"), 2, False)) Then
    CurAppID = Mid(CurBarcodeSTR, 1, 2)
ElseIf Application.IsNA(Application.VLookup(CurAppID, Sheets("Parameters").Range("E4:K21"), 2, False)) And Application.IsNA(Application.VLookup(CurAppID, Sheets("Parameters").Range("E4:K21"), 2, False)) Then
End If

CurBarcodePartDest = Cells(WorksheetFunction.Match(CurAppID, Sheets("Parameters").Range("E4:E21"), 0) + 3, 11).Address
'INFO: Cells("Row", "Column").Address

Einde:
    
End Sub

 

[Norie]

Try using an event other than Change, that event is triggered after every key stroke.

 

[NessPJ]

Try using an event other than Change, that event is triggered after every key stroke.

Thanks for the reply.

I use the _change event because i am also displaying some data to the user as a feedback (of succesful data entry)

CurAppIDLength = Application.VLookup(CurAppID, Sheets("Parameters").Range("E4:K21"), 3, False)

CurAppIDDataLength = Application.VLookup(CurAppID, Sheets("Parameters").Range("E4:K21"), 5, False)

CurBarcodePart = Mid(CurBarcodeSTR, 1 + CurAppIDLength, CurAppIDDataLength)

If CurAppID = "10" Then
    LabelBatchData.Caption = CurBarcodePart
ElseIf CurAppID = "00" Then
    LabelSSCCData.Caption = CurBarcodePart
ElseIf CurAppID = "15" Then
    LabelTHTData.Caption = CurBarcodePart
End If

So if possible, i would very much like to keep using the _change event.

 

[NessPJ]

I fixed it! The data was being entered by use of a barcode scanner and the barcode scanners i use always give an Enter (carriage return) when a barcode was scanned successfully.
So now i wait for this Enter to occur, before i start to dissect the data input.

If InStr(1, TextBoxInvoer.Text, vbCr, vbTextCompare) > 0 Then
GoTo ScanGedetecteerd
Else
GoTo Einde
End If