Excel to beep if cell is empty.

I am using a bar code scanner to scan data into cells $G6 and below. When the code is entered the cell next to it $F6 looks up a data list on another sheet to find that bar code with the name for that item listed in the column next to it and pastes it in that cell ($F6) with the following code:
=IFERROR(VLOOKUP($G6, 'Drop Down Data'!$B$2:$C$1048576, 2, FALSE),"")
Is there a way I can have it beep if it doesn't find the serial.

So basically if im say scanning in 100 items, as soon as it comes across a bar code it cant find the computer should beep to let me know the item name needs to be entered manually.

Thanks,
James

Try something like this in the modeule for the sheet you are working on. It works in my windows computer

Please Login or Register  to view this content.

Thanks

Thank you for you response.
I think I must be doing something wrong, cus it doesn't seem to be working for me.
I entered the code into the the worksheet im working on, and changed the range to G10:G1048576 (this is the column that the serial number is scanned into).
I dont see in the code where it specifies the cell (one to the left in F column) which should triggers the beep when that cell doesnt find a data match from the serial number entered in G?

Hi,

Your code should look like this

Please Login or Register  to view this content.

The word offset means get the value of the cell that's 1 column to my right. target.offset(manyrows,manycolumns)

and the code should be in the sheet ou are scanning to module. In the picture sheet1 module is highlighted.

Capture.PNG

I think were close, however it now beeps every time I enter data into a G cell (weather VLOOKUP in cell F finds data or not). I feel like it takes 1/2 a second for the data to come up, so it may just be that VB is checking before the F cell gets a chance to update if thats possible. If that is the case is it possible to just delay the VB slightly maybe?

what kind of values does the Vlookup return?

Values, numbers, specific amount of digits? I am trying to find another way to identify the empty value.

Thanks

Its not really specific. Its looking up names for items of equipment. So basically on another page there are two really long rows of barcode with the name for that item in the column next to it. To save the user from having to manually enter the item when the barcode is scanned into the G cell, the F cell looks up that barcode on the on the other page to find the item name. This is the code it is using to find it:
=IFERROR(VLOOKUP($G6, 'Drop Down Data'!$B$2:$C$1048576, 2, FALSE),"")

So basically when the user is scanning say 100 items at a time they wont know until they get back to the computer if it didnt find an item in the list, and it would take them ages to locate which item that is (since the equipment is all different, and not scanned in any particular order). If the computer beeps to tell the user it cant find the item name then they can manually enter it there an then (since that item will still be in there hand).

OK I figured out how to delay it by 2 seconds and passing and argument. A headache. I got it from here

So now we need the even under the main sheet:

Please Login or Register  to view this content.

and in another module the beepsound code

Please Login or Register  to view this content.

With this we are delaying the action by 2 seconds. Try it and let me know if it worked

Its working! When i first entered the code with the delay it didnt work. It then just dawned on me that it might be referencing the wrong cell, so I put data in the cell to the right instead and it didnt beep. So I went back to the original code (no delay) and changed the 1 to a -1 and now its working.
Thank you so much for your help!

Sorry, one last thing I just noticed. If I erase the numbers in the cell it also beeps. Is there something I can add to stop that (so it only check the next cell when adding data to the first one, not clearing it)?

This should take care of it

Please Login or Register  to view this content.

Thanks.

If you make any modification please post your final code here so we all now how the problem was fixed and others can learn.

Thanks

Works perfectly, thank you.

I am glad I was able to help

Sorry, I spoke a little too soon. I just discovered a small qwerk with it. Say if im deleting an entry, if I erase the Item name and serial number cells (G) at the same time then it brings up a Run-Time error 13. If I erase one cell first, then the second cell its ok. Is there a way to prevent the error when clearing both cells at the same time?

whats the code being used and what line gets highlighted in yellow?

thanks

This is the code being used. I added the code to display the message box, and then bring up an input box to allow the user to enter the new record. Everything works fine for data entry when a record isnt located. It just brings up the error when I try to delete the record.


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("G10:G1048576")) Is Nothing _
Or Target.Cells.Count > 1 _
Or Target.Value = vbNullString Then Exit Sub

If Target.Offset(0, -1).Value = vbNullString Then
Beep
MsgBox ("Bar code not found, please enter item manually.")
ActiveCell.Offset(-1, -1).Value = InputBox("New item name.", "Item Name.")

End If

try this untested

Please Login or Register  to view this content.

please use code tags # when entering code, it makes it easier to read and copy

Thanks

That did it, lets me erase it without any issue now. Thanks for sticking with me.

You are welcome