Excel VBA获取完整的文本框值

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:

我是VBA的新手。我有一个使用Excel的条形码扫描器填充的文本框。表单上有不同的值,例如:

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?

我需要检查代码是否包含'F'然后执行某个操作,否则执行其他操作。我在Textbox_Change()函数上执行此操作,这会导致首先触发608001代码。如何让它首先触发带有'F'的代码?

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.

我决定更清楚地说明发生了什么。当使用_Change()函数时,首先满足字符6到达时的条件,这意味着代码被视为608001而不是608001F。

5 个解决方案

#1

After clarifying with OP, the problem is this:

用OP澄清后,问题是:

• 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:

...将标准代码添加到与Change事件无关的宏:

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.

...当代码扫描程序开始写入文本框时,您“激活倒计时”(基本上,您安排个人宏在3秒内启动)。因此,在3秒运行之前,代码扫描程序将完成编写代码并且您不会遇到同样的错误。

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).

当然,3秒是模拟值;将代码扫描程序写入框中的速度可能会快得多(在这种情况下,例如减少到1秒)或更慢(在这种情况下,例如增加到5秒)。

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

#2

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.

如果是这样,使用SPLIT()函数,传入vbcrlf以获取每个条形码的数组,每行文本并循环遍历它们。

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.

如果您的意思是文本框一次只保留其中一个代码而608001替换608001F,则使用私有变量来存储以前的文本框值。

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

#3

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.

您可以使用InStr()函数查找字符串中是否存在字符,如果字符不存在,则函数返回0。然后只需检查返回值是否大于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

#4

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".

如果我理解了你的问题,你似乎不希望触发Change事件,直到你没有给出某种确认来说“我完成了插入值”。

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):

在这种情况下,我可能建议使用“”分隔符编写代码(即,直到你没有按SpaceBar确认成功插入,没有任何反应)。为了实现这一目标,您只需添加两行新代码(请参阅下面的编辑):

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...

1)你写的是608001 ......

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

之前:触发了Len()= 6条件,因此您没有时间最终输入F in;

Now: waiting for user confirmation

现在:等待用户确认

2) You write 608001F...

2)你写的是608001F ......

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

之前:Len()= 7会被触发,但你没有时间输入最后一个字符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.).

3)按SpaceBar - >代码开始运行,你不会再将6 char字符串与7 char字符串混淆。请注意,按下SpaceBar意味着“我正在提交我的条形码”。这意味着,您可以将SpaceBar字符替换为任何其他Chr()集合(Enter,数字,字母等)。

#5

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