macro for library inventory

[technomancer]

Hello,
I am using Excel to inventory a small academic library. We have a list of almost 50,000 items in call-number order with their unique barcodes in col. A of an Excel sheet. In the past I have used the "Find and Replace" feature by going to the shelves and scanning each book's barcode into the "find what" box and using "replace with" to change the found item's cell fill color to green. In this way, any item not found or out-of-sequence will show up as a white gap in col. A. The students who have to do the scanning think this is slow drudgery and I have been trying to write a macro which would do the same thing without having to click the "replace" button each time and click in the find box for the next scan. This is my first attempt at writing code and all I have succeeded in doing so far is to make an input box and a button to bring it up. What I need is a macro for scanning the barcodes into the box one after another, finding each match in column A and coloring each cell green and returning to the input box for the next scan. Can anybody help me with this macro?
technomancer

[NoSparks]

Have a look at
viewtopic.php?f=3&t=7172
perhaps you can glean something from the file I posted there.
ask any questions you have, we're anxious to help.

[pecoflyer]

I tried to modify it for my purposes, but then I found a short file that partly works for me, except I have to scan the barcode into cell A1 and it doesn't return to cell A1 for the next scan. Can you tell me how to modify this so I can use my existing input box? This is the code from sheet1:

Code: Select all

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then Exit Sub
Dim A As Range
Dim LR As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
Set A = Range("A2:A" & LR).Find(Target.Text, LookIn:=xlValues)
    If Not A Is Nothing Then
      C.Interior.ColorIndex = 4
    End If
End Sub

My input box is in the Forms folder and is called frmInvScan.

My code for button to invoke the input box is Module 2:

Code: Select all

Sub Rectangle1_Click()
frmInvScan.Show
End Sub

Please forgive if I haven't made myself understood: I'm very new to all this vocabulary. Thanks! technomancer

[NoSparks]

Sounds like you're scanning into a textbox on a user form.
That needs to be handled a little differently than scanning directly into the same cell of a worksheet every time but should be no problem.

Will need to see what you are actually working with.
If you post a sample of the file I'll gladly have a look.
If the file is too large for this forum you could zip it or strip out most of the 50,000 existing data.

[technomancer]

Here's a little sample of my 50,000-item list. I used to scan the barcode from each book into the Find and Replace box but there were too many manual steps for efficient use of time. The code in my last post scans the barcode into A2 (the cell directly below the heading "Barcode").

BARCODE ITEM TYPE CALL NUMBER TITLE
1050077143 Cassette BF408 .G37 1999 How to think like Leonardo Da Vinci [seven steps to genius every day] /
1050077440 Cassette BF637.S8 .C683 1992 Living the seven habits applications and insights /
1050077517 Cassette BL600 .S62 1993 Nature, magic, and community the way of the Dagara /
1050077144 Cassette BL624 .P432 1997 Road less traveled and beyond
1050077081 Cassette BL627 .C39 1988 Meditation
1050077155 Cassette DA930.5 .C342 1997 How the Irish saved civilization
1050077158 Cassette DD247 .W83 1982 [My Life in Germany]
1050077429 Cassette DS354 .Z69 2002 Zoya's story [an Afghan woman's struggle for freedom] /
1050077154 Cassette DS919 .T78 1973 Truman vs. MacArthur
1050077159 Cassette E99.A6 H42 1986 [History, background and future of the Mescalero Apache tribe]
1050077160 Cassette E99.A6 H42 1986 [History, background and future of the Mescalero Apache tribe]
1050076273 Cassette E169.04 .H432 1999 River-horse [across America by boat] /
1050077414 Cassette E745 .M67 1991 American military experience in World War II and Vietnam Robert E. Morris.
1050077415 Cassette E745 .M67 1991 American military experience in World War II and Vietnam Robert E. Morris.
1050076274 Cassette E860 .A44 American crisis the last days of the Nixon presidency.
1050060806 Cassette E873.2 .C373 2001b Christmas in Plains
1050076277 Cassette F596 .V64 1974 Voices of the Old West
1050077420 Cassette F598 .D8 1997b Lewis & Clark
1050077431 Cassette F737.M4 D643 2000 This house of sky landscapes of a Western mind /

I want to modify the code so the scan goes into the user form which comes up when a button is clicked, but I don't know how/where to insert the code which tells it to scan into the user form instead of a cell.

Thanks for reading and responding to my posts!

[NoSparks]

If the goal is to reduce the manual steps,
replace your first code with this and forget about the user form.

Code: Select all

Private Sub WorkSheet_Change(ByVal Target As Range)
'only allow A2 to run this macro
If Target.Address <> "$A$2" Then Exit Sub

'declare variables
    Dim fndScan As Range
    Dim LR As Long

'get last row of column A
LR = Cells(Rows.Count, "A").End(xlUp).Row

'find that scan in column A, below the scan into cell
Set fndScan = Range("A3:A" & LR).Find _
                (What:=Target.Text, _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)

    'if found the range will not be nothing
    If Not fndScan Is Nothing Then
      'set color index of found range
      fndScan.Interior.ColorIndex = 4
    Else
        'if not found give message
        MsgBox "Scan  " & Target.Value & "  not found."
    End If
   
'turn off events so this macro doesn't call itself
Application.EnableEvents = False
'make A2 the active cell
Target.Activate
'clear cell ready for next scan
Target.ClearContents
'turn events back on
Application.EnableEvents = True

End Sub


If you still want to go the user form route we can do that.

[NoSparks]

Here's a sample file with a user form.
I'm guessing the scanner will feed the scan into whatever is active within Excel.

Would really appreciate if you would give it a try and let me know.

Library_Scanning_Userform.xlsm

[technomancer]

You're right, our scanners can scan the barcode into any Excel sheet or Word doc. I tried these and they're both very good. I tend to favor the userform one because the box really calls attention to itself as the place for the scan to go (otherwise our students might just scan the barcode into any cell, replacing the contents and making a big mystery for me to sort out). The only little issue with the userform is the "accept" button. Can it be done so the act of scanning the barcode in is the only command it needs to accept the scan? (Saves a few thousand clicks a day). If not, I think we'll use the first one and skip the box.

Heartfelt thanks!

[NoSparks]

No problem, simply moved everything the button did to the text box's Change event.
If you don't want the close button either, just delete it from the form.

Library_Userform_AutoEntry.xlsm

Thanks for the feedback on where the scanned info goes.

[technomancer]

Thanks for your response. I tried this one and it didn't work--the cell does not get selected or change color. You can see the "not found" box flash for just about a millionth of a second but that's all that happens. I compared the code with the code that works and can't see anything that would make it act differently...