CODEWITHSUNDEEP
vbaexcel
Jatin
Jatin

Reputation: 31724

To detect and trigger event - Excel

I have a barcode scanner. So when the barcode is scanned using a gun, the value normally is entered to textfield placed on the screen.

So I have made an excel macro. And when the barcode is scanned, the text appears on the cell ending with the new line character (text+"\r\n" is provided by the scanner itself). So an event is triggered and does the rest.

The problem is that the event is only triggered with a new line character. What if the barcode scanner didn't provide \r\n after every barcode output. How do I deal with such situations? How do I detect it and trigger event?

Upvotes: 0

Views: 4024

Answers (2)

InContext
InContext

Reputation: 2501

add a Worksheet_Change event as follows into the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    ' Add code here to check for
    ' new line and add if not present

End Sub

Or if you have the data in a Userform textbox then use the following:

Private Sub TextBox1_Change()

    ' Add code here to check for
    ' new line and add if not present

End Sub

Upvotes: 0

SeanC
SeanC

Reputation: 15923

If you create a userform to take the input, you can use the keydown or keypress events to detect keys pressed. You would have to know what the end condition of the barcode would be, so you know when to send the value to the cell to be processed.

Keydown will give you shift state, and also capture pageup/pagedown/other special keys

Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    ByVal Shift As Integer)

Keypress will only work with keys that produce a typeable character

Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Without userforms, you will have to rely on enter/newline to trigger the Change event in the worksheet

Upvotes: 2

Related Questions