Auto-Activate Workbook when using a Barcode Scanner

[NickRed18]

Good Morning Everyone (at least whenever I am first posting this),

I have made a barcode scanner userform that acts as a small inventory system for my company. Nothing special but it gets the job done. Basically the inventory employee would scan a specific parts' barcode, and a TV display will turn Red for that part - indicating the part is low in stock.

The last issue I am trying to resolve is a way to activate or "set focus" to the workbook whenever a barcode is scanned. If someone is using the computer in inventory (which is where the workbook will always be open and ready), how can I have it to where whenever the barcode trigger is pressed, the Workbook becomes the active window on the screen to accept the barcode? Is there a way for VBA to detect and input like that?

 

[Veritan]

Hi Nick, I also use barcode scanners in my company, so I know a little bit about them. The thing with a scanner is that you need to think of it like any other input device, such as a keyboard or a mouse. Barcode scanners come with a manual that has several barcodes in it that you can scan to adjust the settings, so some of this may be slightly different depending on your settings. But in general, when you press the trigger to scan a code, the scanner acts like a keyboard and "sends" the value of the barcode along with a keypress of the Enter key. It ends up being the exact same result as if you were to type the value of the barcode in and then press Enter. So if you have Excel open, whatever cell you have selected will end up receiving the text of the barcode, and then the cursor will move one cell down. Since the scanner is an input device, to my knowledge, there is no code that will activate a workbook when the scanner is used. It'd almost be like trying to automate the mouse to double-click the file to open it. Once the Excel sheet is open and is the active workbook, you can do all sorts of things with the Worksheet_Change event in a sheet. For example, on one of my projects, whenever a certain cell receives a value from the scanner, it pops up a UserForm and continues from there. However, I have to manually make sure that the correct file is open and active. I can't automate that from the scanner. If somebody can come up with a way to do this, please feel free to correct me. But to my knowledge, you'll just have to make sure your employees know to keep the workbook open and active.

 

[NickRed18]

Veritan,

Thanks for the reply!
I've done a fair bit of research on using barcode scanners with excel. I'm using a basic userform with a textbox that always has the Focus set to it to allow the barcode to be inserted there everytime, I'd love to send you my workbook to get your thoughts on it if you want.

My number one goal in this project is to make the system run with as little user-input required. My first instinct was no, I wouldn't be able to simply "activate" the workbook whenever the scanner is used - like you said the scanner is simulating a user typing in the code and pressing enter.
So instead of this:

If ScannerTrigger = True Then                      'If the barcode scanner trigger is pressed
    Workbook.SetFocus
End If

Do you know a way to code something like this:

If Workbook.Active = False Then     'If another window becomes the active one
    Wait 5 minutes                  '5 Minute timer before reverting focus back
    Workbook.SetFocus
End If

 

[Veritan]

Well, I did find this thread that talks about setting up custom event for when your workbook loses focus. Feel free to look it over, though it seems somewhat complex to me. What I did do was adapt some code I have for running a clock inside Excel. While I haven't tested this, (so please be careful with it), I believe this will basically check every 5 minutes and make sure that your workbook is the active one. It doesn't detect when the workbook has lost focus, but instead just activates it every 5 minutes. The StopFocus sub is there in case you need to disable it for some reason (maybe you could assign it to a button on the workbook?) Please note that the initial call to the SetFocus sub occurs whenever you open the workbook. You may want to put it in something else, such as when a sheet is activated.

Option Explicit
Dim RefreshTime As Date


Private Sub Workbook_Open()
    Call SetFocus
End Sub


Sub SetFocus()
    ThisWorkbook.Activate
    Call SetTime
End Sub


Private Sub SetTime()
    RefreshTime = Now + TimeValue("00:05:00")
    Application.OnTime EarliestTime:=RefreshTime, Procedure:="SetFocus"
End Sub


Private Sub StopFocus()
    On Error Resume Next
    Application.OnTime EarliestTime:=RefreshTime, Procedure:="SetFocus", Schedule:=False
    On Error GoTo 0
End Sub

 

[NickRed18]

I'm glad you provided me with your bit of code, I looked at that thread and the replies were going WAY over my head.
It seems like this code does not set Excel as the active application running on my screen, but it just activates the workbook. So it does not help me much, but thank you very much for the help anyway - I still learned something about setting focus on a timer!

 

[Veritan]

Glad it helped. You could look into adjusting the code in the SetFocus sub, since the only line in there that's doing anything with the workbook is the ThisWorkbook.Activate line. You could add/edit any other lines to it and they'll occur on the 5 minute timer. Maybe see if you can find a line that will set Excel as the active application?