macro for library inventory

[technomancer]

Addition to previous post: if I scan a barcode several times in quick succession, I can see it says "1" not found--like it didn't wait for the whole barcode which is a string of 10 digits, always beginning with "1".

[NoSparks]

like it didn't wait for the whole bar code which is a string of 10 digits

That would definitely be a problem. Sounds like the scanner is sending numbers one at a time and the text box change event is firing before the next number arrives.
If that is the case I guess we can count the digits if it's always 10.

Did the userform version work when you had to use the Accept button ?
Did the version without the userform work properly ?

[technomancer]

Yes, both other versions you sent me worked fine. Thanks.

[NoSparks]

Hmm.... interesting that both the others worked. Must be a timing issue between firing the text box change event and getting the full scan into that text box.

Unfortunately I can't test anything before suggesting it so this is my next best guess.
Try replacing the code for the txbScan_Change with this, and let me know.....
(Keeping my fingers crossed.)

Code: Select all

Private Sub txbScan_Change()
    Dim fndScan As Range
   
If Len(txbScan.Value) >= 10 Then

    With Sheets("Sheet1").Columns(1)
        Set fndScan = .Find(What:=Me.txbScan.Text, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
    End With
   
    If Not fndScan Is Nothing Then
        fndScan.Interior.ColorIndex = 4
    Else
        MsgBox "Scan   " & Me.txbScan & "   not found."
    End If
   
    With Me.txbScan
        .Value = ""
        .SetFocus
    End With
End If

End Sub


[technomancer]

Tried the latest version. The numbers will now scan in. The first found cell turns green as expected. Then the blinking cursor does not return automatically to the scan box; I have to click before I can scan again. If I scan a barcode that does not exist on the list, the message box flashes by too fast to read, unless rapidly repeated, when you can see it says "(entire 10-digit barcode) not found".

I have tried a lot of tweaks on this version, but haven't found the right one yet!

Many thanks!

[NoSparks]

Things sure sound goofy to me. Don't know how the message box can be flashing by too fast to read. The procedure will stop at the point where the message is displayed and not carry on until the message box is dismissed by either Enter or mouse click.

Because I can't actually test anything, I don't know what to say.

Does anything happen if either the greater than or equal sign on the If Len(txbScan.Value) line is removed or the 10 is increased ?
My thinking here is that the scanner maybe sends some non-displayable character(s) along with the numbers.

Perhaps set a break point on the If Len(txbScan.Value) line
Do a scan and the procedure code will come up stopped with that line highlighted, ready to execute
Use the F8 key to step one line at time through the procedure and see where things go
Swap back and forth between the Excel screen and the VBA screen with each step and observe what's happening.

Other than that I can only suggest you use the form with the Accept button.
Even that shouldn't be that bad. You don't have to use the mouse to accept, just hit Enter twice.

Sorry I can't do any more than that.
Good luck.

[NoSparks]

One last kick at the can... if I may.

Could I get you to take the first file I posted, that scans directly into the same cell every time,
add a new sheet,
scan of a bunch of different bar codes each into separate cells in column A of the new sheet,
save the file and post it back to the forum here ?

Maybe I can analyze what's being scanned in and figure out what's needed to have things operate as desired.

Thanks
NoSparks

[technomancer]

I believe our scanner does place an invisible asterisk (*) at the beginning and end of each barcode, to tell the scanner to begin and end. I added 2 to the expected number of characters but nothing seemed to happen.

When I scan a number not found on the list, the msg. box flashes by too fast but the cursor remains in the textbox ready for next scan. When a barcode that is on the list is scanned it turns the proper cell green but the box has to be reactivated for the next scan.

As much as I like to tweak something until it's perfect, I fear I may soon have to decide to go with the one that scans into the A2 cell--it really works just fine. (The boss, you know, she expects me to have something usable!)

I'll get that sheet you requested ready to upload as soon as I can!

Thanks.

[technomancer]

Formless_LibraryScanning_Sample.xlsm

Here's the no-userform version with real barcodes scanned in. See what you can make of it or, if you're sick and tired of it, just let it go--as I said it does work just fine as-is. Sometimes when I fiddle with Excel for weeks I later find out that Excel simply can't do what I want it to. (Such as displaying diacritics correctly).

I appreciate all your help!

[NoSparks]

I'm not able to glean anything from the file so I will just let it go, although it leaves kind of an incomplete feeling.

Oh well....
I'm glad you got something you can work with
and look forward to your next challenge.