I have a workbook that has 4 different sheets for inventory purposes (each sheet corresponds to a type of inventory to make organizing easier). Not the best setup, but I have a barcode lookup system where I scan the item's barcode and Excel finds and highlights the corresponding row (containing info like name, picture, quantity, etc.) that's all updated manually. Originally everything was on one sheet but recently I have sorted them out into 4 different sheets. Since then the barcode lookup only works on the original inventory sheet (makes sense since the code was only for that one sheet). I have been unable to figure out how to change the code to work on the whole workbook. I have tried to change worksheet to workbook (didn't work), then I tried to add a Set ws = ThisWorkbook.Sheets("")
for each sheet (also didn't work) and a handful of other changes. If anyone has any idea on how to change it so it searches the workbook instead of the one sheet I would appreciate it.
Here is a copy of the working code for the first sheet:
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Inventory List")
Dim rangeToLook As Range
Set rangeToLook = ws.Range("C3:C1000")
Dim wholeRange As Range
Set wholeRange = rangeToLook.Resize(, 10)
' change 14408667 to yours grey color code here
wholeRange.Cells.Interior.Color = 14408667
Dim code As Variant
code = InputBox("Please scan a barcode and hit enter if you need to")
Dim matchedCell As Range
Set matchedCell = rangeToLook.Find(what:=code, LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=True)
If Not matchedCell Is Nothing Then
With matchedCell
Application.Goto .Cells(1)
.Resize(1, 10).Interior.ColorIndex = 20
End With
Else
MsgBox "Barcode Not Found"
End If
End Sub
Thank you for the help in advance.
Try this code:
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rangeToLook As Range
Dim wholeRange As Range
Dim code As Variant
Dim matchedCell As Range
code = InputBox("Please scan a barcode and hit enter if you need to")
For Each ws In ThisWorkbook.Sheets
Set rangeToLook = ws.Range("C3:C1000")
Set wholeRange = rangeToLook.Resize(, 10)
wholeRange.Interior.Color = 14408667
Set matchedCell = rangeToLook.Find(what:=code, LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=True)
If Not matchedCell Is Nothing Then
With matchedCell
Application.Goto .Cells(1)
.Resize(1, 10).Interior.ColorIndex = 20
Exit For
End With
End If
Next
If matchedCell Is Nothing Then
MsgBox "Barcode Not Found"
End If
End Sub
I tried this and unfortunately, it didn't work at all... it asked me to input a barcode, I did, and then it went and asked another 4 times (once per sheet without letting me stop the process) while selecting and highlighting empty cells on each page and not the one item I was looking for.
This one looks like it works great. The only question I have left is that after finding the item (it does find it and pick the correct cell), I'm always getting the pop-up for barcode not found (even though it did find it). I don't see anything in the code from what I understand of it that would make it do that.
You understand this more than I do, so I'm beyond lost on this one. It doesn't look like anything should cause it.. It happened every single time, it might be that another code I have is causing an issue (I have another code that moves selected items to the bottom of the list if it is discontinued and another one to automatically resize the photos to fit in the cells). I'll have to play around with it a bit and I'll leave any updates if I get anywhere.
@intheend555 - please try the updated version. I added 2 lines. which essentially check the same thing, but differently. Maybe this will help.
@ZygD the new code didn't fix it so I changed it back to the other one you put up and I inserted
Exit For
right after.Resize(1, 10).Interior.ColorIndex = 20
like @ FaneDuru posted and it looks like it now runs without any problems.