VBA - Userform Validate Text in TextBox?

NessPJ

Active Member
Joined
May 10, 2011
Messages
395
Office Version
  1. 365
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...

VBA 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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
Try using an event other than Change, that event is triggered after every key stroke.
 

NessPJ

Active Member
Joined
May 10, 2011
Messages
395
Office Version
  1. 365
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)

VBA Code:
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

Active Member
Joined
May 10, 2011
Messages
395
Office Version
  1. 365
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.

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

Watch MrExcel Video

Forum statistics

Threads
1,127,596
Messages
5,625,714
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top