0

I am new to VBA. I have a textbox that is being populated by bar code scanner using Excel. There are different values on the form, for example:

608001F
608001
001IN

I need to check if the code contains 'F' and then do a certain action, otherwise do another action. I do this on Textbox_Change() function, which is causing the 608001 code to be triggered first. How can I make it trigger the code with the 'F' in it first?

My code looks like this (edited for brevity, so excuse if any syntax errors):

Private Sub TextBox1_Change()
Value = TextBox1.Value
If Value <> "" Then
    If (Len(Value) = 7 And Right(Value, 1) = "F") Then
        ActiveCell.Value = Value
        ActiveSheet.Cells(ActiveCell.Row + 1, 1).Select
        TextBox1.Value = ""
        TextBox1.Activate
    ElseIf (Len(Value) = 6 Or (Len(Value) = 5 And (Right(Value, 2) = "IN" Or Right(Value, 2) = "EM"))) Then
        ActiveCell.Value = Value
        Selection.Offset(0, 1).Select
        TextBox1.Value = ""
        TextBox1.Activate
    Else
        'Do nothing
    End If
End Sub

EDIT

I decided to clarify what is happening better. When using the _Change() function, the condition when I reach character 6 is fulfilled first, meaning the the code is seen as 608001 instead of 608001F as should be the case.

Improve this question
6
  • how do you fill your TextBox1 with the values? Commented May 11, 2015 at 9:40
  • Scanning by bar code scanner. Scan a value, perform actio, then clear textbox, and scan next value. Commented May 11, 2015 at 9:55
  • Is the problem that the _Change() event is getting triggered on every new character that the scanner 'types' into the textbox? You might need to use some sort of time delay to wait for the data entry to complete, or see if your scanner has options to add a terminating character that you could then detect and strip from the resulting string. Commented May 11, 2015 at 10:07
  • nekomatic, thank you - you understand the problem correctly. I cannot change the scanner options, as the papers (all 75000 of them) are already printed, so the papers are as they are. I therefore would have to look into a delay mechanism, but not sure how to do this. I guess what I am looking for is if no new character is entered for 100ms or something, then perform the action. Any ideas? Commented May 11, 2015 at 10:11
  • 1
    You don't need to change the papers to add an extra char. You can configure barcode scanners to transmit extra characters - suffix. One possibility is to add enter as suffix and handle AfterUpdate event. Commented May 11, 2015 at 10:22

5 Answers 5

Reset to default
1

After clarifying with OP, the problem is this:

  • The string is not typed by the user manually, but by a code scanner which "works as a person" typing letter by letter;
  • The inserted string might trigger a wrong part of the code without being still finished.

A possible solution coming to my mind is a timer which is waiting for the code scanner to write the code into the box, then running the code. Basically:

Declare a global variable

... to remember if the countdown has started yet or not. 

Dim started As Boolean

Write your code into another macro

... add the standard code to a macro which is not related to a Change event: 

Private Sub TextBox1_Change_Personal() '<-- not triggered if TextBox1 is changed!
Value = TextBox1.Value
If Value <> "" Then
    If (Len(Value) = 7 And Right(Value, 1) = "F") Then
        ActiveCell.Value = Value
        ActiveSheet.Cells(ActiveCell.Row + 1, 1).Select
        TextBox1.Value = ""
        TextBox1.Activate
    ElseIf (Len(Value) = 6 Or (Len(Value) = 5 And (Right(Value, 2) = "IN" Or Right(Value, 2) = "EM"))) Then
        ActiveCell.Value = Value
        Selection.Offset(0, 1).Select
        TextBox1.Value = ""
        TextBox1.Activate
    Else
        'Do nothing
    End If
    started = False '<-- reset control variable for next code
End Sub

Activate the macro after 3 seconds

... when the code scanner will start writing into the textbox, you "activate a countdown" (basically, you schedule your personal macro to start in 3 seconds). So, before the 3 seconds run, the code scanner will have finished to write the code and you don't fall into the same mistake. 

Private Sub TextBox1_Change()
    If started = False Then
        Application.OnTime TimeSerial(Hour(Now()), Minute(Now()),Second(Now())+3), "TextBox1_Change_Personal"
        started = True '<-- deactivating control variable to avoid re-scheduling each time
    End If
End Sub

Of course, the 3 seconds are a mock value; your code scanner might be much faster in writing the value into the box (in that case, decrease to 1 second for example) or slower (in that case, increase to 5 seconds for example).

Final (should work) code

Dim started As Boolean
Private Sub TextBox1_Change()
    If started = False Then
        Application.OnTime TimeSerial(Hour(Now()), Minute(Now()),Second(Now())+3), "TextBox1_Change_Personal"
        started = True '<-- deactivating control variable to avoid re-scheduling each time
    End If
End Sub
Private Sub TextBox1_Change_Personal() '<-- not triggered if TextBox1 is changed!
    Value = TextBox1.Value
    If Value <> "" Then
        If (Len(Value) = 7 And Right(Value, 1) = "F") Then
            ActiveCell.Value = Value
            ActiveSheet.Cells(ActiveCell.Row + 1, 1).Select
            TextBox1.Value = ""
            TextBox1.Activate
        ElseIf (Len(Value) = 6 Or (Len(Value) = 5 And (Right(Value, 2) = "IN" Or Right(Value, 2) = "EM"))) Then
            ActiveCell.Value = Value
            Selection.Offset(0, 1).Select
            TextBox1.Value = ""
            TextBox1.Activate
        Else
            'Do nothing
        End If
        started = False '<-- reset control variable for next code
    End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Perfect! It is very elegant. Solves my problem.
0

Is it so that the string below is all in the same, single textbox?

608001F
608001
001IN

If so, use the SPLIT() function, passing in vbcrlf in order to get an array of each barcode, each line of text & loop through them.

If you're meaning the textbox only holds one of those codes at a time and 608001 replaces 608001F, then use a private variable in order to store the previous textbox value.

Private m_earlierBarcodeVal As String

Private Sub UserForm_Initialize()
    m_earlierBarcodeVal = TextBox1.Text
End Sub

Private Sub TextBox1_Change()
    MsgBox "Old value= " & m_earlierBarcodeVal & vbCrLf & _
    "New value= " & TextBox1.Text

    m_earlierBarcodeVal = TextBox1.Text
End Sub
Improve this answer

1 Comment

No. The strings are on a printed paper. I am selecting the textbox, then scan the value on the paper. Based on what the value contains, it either moves to the next column, or moves to the next row, and clears the textbox for the next scan entry.
0

You can use the InStr()function to find if a character exists in the string, the function returns 0 if the character doesn't exist. Then simply check if the return value is greater than 0.

This simple example should give you a clue.

Sub lookForF()

    Dim inVal As String

    inVal = Range("A4").Value

    If InStr(1, inVal, "F") > 0 Then

        'Your code here when F exists
    Else

        'Your code here for other case

    End If

End Sub
Improve this answer

3 Comments

My code already can detect if 'F' is in the code, but the problem is that the other code is found FIRST. I need the code with 'F' in it to be found first. Since the textbox checks _Change() function, when reaching character 6, it triggers the event, as the codes are the same, except for the F at the end. I need to find the F first.
If you use instr() you can skip the Len.
Thanks, Peter, but that will not work, because on change of the field, when getting to "608001" the second condition will be triggered, before I can get a chance to enter the F.
0

If I understood your problem, it seems you don't want the Change event to be triggered until you don't give some sort of confirmation to say "I finished inserting the value".

In that case, what I might suggest is to write the code using a " " separator (i.e. until you don't press SpaceBar to confirm the successful insertion, nothing happens). In order to reach this, all you need is adding two new lines of code (see edit below):

Private Sub TextBox1_Change()
Value = TextBox1.Value
If Value <> "" Then
If Right(Value,1) = " " Then '<-- new line
    If (Len(Value) = 7 And Right(Value, 1) = "F") Then
        ActiveCell.Value = Value
        ActiveSheet.Cells(ActiveCell.Row + 1, 1).Select
        TextBox1.Value = ""
        TextBox1.Activate
    ElseIf (Len(Value) = 6 Or (Len(Value) = 5 And (Right(Value, 2) = "IN" Or Right(Value, 2) = "EM"))) Then
        ActiveCell.Value = Value
        Selection.Offset(0, 1).Select
        TextBox1.Value = ""
        TextBox1.Activate
    Else
        'Do nothing
    End If
 End If '<-- new line
 End If
End Sub

Like that, your code will work like this:

1) You write 608001...

Before: The Len() = 6 condition was triggered so you didn't have the time to eventually type the F in;

Now: waiting for user confirmation

2) You write 608001F...

Before: The Len() = 7 would have been triggered, but you didn't have the time to type the last char F;

Now: waiting for user confirmation

3) Press SpaceBar --> the code starts running and you will not confuse the 6 char string with the 7 char string any longer. Please note that pressing the SpaceBar means "I'm submitting my barcode". Which means, you can replace the SpaceBar character with any other of the Chr() collection (Enter, a number, a letter etc.).

Improve this answer

5 Comments

Hi Matteo, data is scanned in with a bar code scanner. The idea is to make capturing as easy as possible. Scan, let the macro do the work, then scan again. The administrators have to scan 75000 papers, so having to manually add a space after each field was scanned, would delay them immeasurably.
@KobusMyburgh ah, but is this code scanner adding the characters one by one?
Hi Matteo NNZ yes, it does. The scanner works like a keyboard, as if you would be pressing keys on the keyboard. If I am not mistaken, majority of bar code scanners work like this.
@KobusMyburgh ok, I'll try to think about it and draft another solution I have in mind.
@KobusMyburgh, I've added an answer, please try it out and let me know if it works.
0

Something like this perhaps then? EDITED after better understanding the problem (see reply comments below):

Private m_previousBarcodeString As String
Private m_isFirstBarcodeEntered As Boolean

Private Sub UserForm_Initialize()
    m_isFirstBarcodeEntered = True

    ' Some test data only. You don't need to use the rest of this
    ' method code below as you have a barcode scanner.
    Dim barcodesArray(2) As String
    barcodesArray(0) = "608001F"
    barcodesArray(1) = "608001"
    barcodesArray(2) = "001IN"

    Dim barcodeIndex As Integer
    Dim barcodeCount As Long
    Dim charIndex As Integer
    Dim charCount As Integer
    barcodeCount = UBound(barcodesArray)

    For barcodeIndex = 0 To barcodeCount
        charCount = Len(barcodesArray(barcodeIndex))

        For charIndex = 1 To charCount
            TextBox1.Text = TextBox1.Text & Mid(barcodesArray(barcodeIndex), charIndex, 1)
        Next charIndex

        TextBox1.Text = ""
    Next barcodeIndex
End Sub

Private Sub TextBox1_Change()
    If (m_isFirstBarcodeEntered = True) Then
        m_isFirstBarcodeEntered = False
    Else
        If Len(TextBox1.Text) = 0 Then
            MsgBox "Was '" & m_previousBarcodeString & "' ending in 'F'?: " & _
            UCase(isValidBarcode(m_previousBarcodeString))
        Else
            m_previousBarcodeString = TextBox1.Text
        End If
    End If
End Sub

Private Function isValidBarcode(ByVal singleBarcodesString As String) As Boolean
    isValidBarcode = False

    If Len(singleBarcodesString) = 7 Then
        If UCase(Right(singleBarcodesString, 1)) = "F" Then
            isValidBarcode = True
        End If
    End If
End Function
Improve this answer

4 Comments

Hi Alex. Thank you for taking time to answer. There are 75000 papers to scan, so hard coding of the string is not an option. In my example, I showed some examples of what the bar code string might contain. What happens when scanning the bar code, the textbox is cleared, as per my original code, so the value could contain any one of those codes at any given time during the scanning, but will contain only one of the codes, as the textbox is cleared after the scan.
Hi Kobus, I was just trying to programmatically reproduce your scenario there when I don't have a barcode scanner with me - you could ignore the UserForm_Initialize() method for your case. Above, I was trying to look for an end of line denoting character (asc 10 or 13) on the textbox change event & call a "lookup whether this is a code ending in F" function when this occurs.
If now I understand correctly, the textbox won't ever contain more than 1 line of text. It'll only contain a value like 608001F OR 608001. In this case, you might be able to use the above edited/changed code instead then.
Hi Alex, thank you again. I have not tested your code, as Matteo's code resolved my problem effectively. Thank you so much for your time. Again, though - your code hard-codes the values, so will not work 100%, but might be adapted slightly to also work, but as Matteo's one works, I am not going to try.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.