VBA Macro/Script help?!?

B

brettd03

New Member
Joined
Apr 13, 2011
Messages
22
I am in desperate need of a code that will play a sound when certain data is entered into a cell.

My day job is with a large distribution facility. With the amount of packages that we process, it is inevitable that a UPS package gets put on a FedEx pallet or visa versa.

UPS tracking numbers all begin with 1z .....FedEx do not.

My goal is to create a permanent barcode for UPS and another for FedEx that are staged on the floor in front of the corresponding pallet. The user will scan the tracking number on the package, and then the barcode that corresponds with the pallet (UPS or FedEx). If the package is put on the wrong pallet, the sound is played.

I dont care what the sound is, but something like the WINDOWS ERROR sound would be great.

For example:

1z45689854663432 is scanned on the package, the floor barcode for FedEx is scanned, an error sound is played (because 1z is not FedEx)

Can anyone help?

Thanks in advance!!
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Open your VBA editor and copy and paste the text into a module and edit the directory path for where the wav file will be located. (This will play hidden files, too, so once you get your WAVs, you can hide them which is what I did.)

That'll do the sound piece, can you do the rest of the VBA or will you need more?

Code: 
Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Sub Sound()
sndPlaySound32 "c:\foldername\sound.wav", 0
End Sub
 
Upvote 0
OK....

I opened a module and pasted this....

Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
Sub Sound()
sndPlaySound32 "C:\Documents and Settings\BMDorsett\Desktop\tada.wav", 0
End Sub


I dont even know if I am doing that right? Where to I declare what will make the sound play assuming I did the module right?
 
Upvote 0
Ok good start.

Next step is go to find the worksheet housing FedEx Packages. Double click on the worksheet, and above the big code window, change the first drop down from "General" to "Worksheet". THen change the second drop down to Change.

It'll insert a new blank sub. Change that code to mirror this:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
     If Left(ActiveCell.Offset(-1, 0).Value, 2) = "1z" Then Sound
End Sub

What that does is after a change is made and excel goes to a new line it takes the location of the active cell, looks back one row and looks at the first two left characters. If the left two characters of the tracking number equal "1z" then it will play the error

Repeat the same steps on the UPS sheet, but make the code look like this. That way, if a tracking number is scanned into the UPS sheet that does NOT lead with "1z" it will play the error sound.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
     If Left(ActiveCell.Offset(-1, 0).Value, 2) <> "1z" Then Sound
End Sub

Hope that helps!
 
Upvote 0
Geez...Amazing! I really appreciate your help.

There are very slight procedural differences though.

There will only be one sheet. There will be two barcodes to scan per package. The first being the shipping label, and the second is the personally generated barcode that signifies UPS/FedEx (almost exactly like going to the grocery check out and buying produce. Produce doesnt always have a barcode, so the cashier scans a barcode that is on a piece of paper is a protective sleeve permanantly fixed the the scanning station) The worksheet will be set up as shown below, with the exception of the EXPLAINATION column. That will not be there.



<TABLE style="WIDTH: 555pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=740 border=0 x:str><COLGROUP><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 7131" width=195><COL style="WIDTH: 409pt; mso-width-source: userset; mso-width-alt: 19931" width=545><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 146pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=195 height=17>SCANS</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 409pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=545>EXPLAINATION</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1z1234567891234</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of label</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>UPS</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of Barcode- correct no error sound .wav</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>123456789</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of label</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>FedEx</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of Barcode- correct no error sound .wav</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1z123789456123</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of label</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>FedEx</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of Barcode- INCORRECT- Error sound .wav will play</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1z1234567891234</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of label</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>UPS</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of Barcode- correct no error sound .wav</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>123456789</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of label</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>FedEx</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of Barcode- correct no error sound .wav</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1z123789456123</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of label</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>UPS</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of Barcode- correct no error sound .wav</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>123456789</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of label</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>FedEx</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of Barcode- correct no error sound .wav</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1z123789456123</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of label</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>UPS</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of Barcode- correct no error sound .wav</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>123456789</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of label</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>FedEx</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of Barcode- correct no error sound .wav</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1z123789456123</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of label</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>UPS</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Scan of Barcode- correct no error sound .wav</TD></TR></TBODY></TABLE>

It is basically saying on one worksheet that if you scan a "1z" and then "UPS" it is correct. If not, the error .wav would play.

Again, thanks for all of your help....it will be a life saver if I can get this to work!

Brett
 
Last edited:
Upvote 0
Try this: instead on the sheet:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveCell.Offset(-1, 0).Value = "UPS" And Left(ActiveCell.Offset(-2, 0).Value, 2) <> "1z" Then Sound
    If ActiveCell.Offset(-1, 0).Value = "FedEx" And Left(ActiveCell.Offset(-2, 0).Value, 2) = "1z" Then Sound
End Sub
 
Upvote 0
REEEAAAALLL CLOSE

The sound plays when UPS Label and UPS barcode is scanned (corret scan).

I need it just the opposite.....
 
Upvote 0
brettd03 said:
GOT IT!!!!!!!!!!!!!!!!!!!!!!!

Thanks!!!!!!!!!!!
Click to expand...

Works great...just one more question....

I am getting a Runtime Error '1004': Application Defined or Object Defined Error.

I choose debug, and the following portion of the code is highlighted:

If ActiveCell.Offset(-1, 0).Value = "UPS" And Left(ActiveCell.Offset(-2, 0).Value, 2) <> "1z" Then

Ideas?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Barcode Scan result is errant in MS Excel, good data in Notepad/G Sheets
Replies
2
Views
3K
bmoreskimore
B
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
124
Fluff
Fluff
M
  • Question Question
Automated recurring calculation. No-one at my job can help me with this.
Replies
0
Views
796
Mr Sandman
M
I
  • Question Question
Assistance with Excel for a semi-automated Attendance Tracking System with validation(s) required
Replies
0
Views
1K
Ian Kok
I
D
  • Question Question
Queueing simulation VBA help
Replies
0
Views
5K
deedeedudu
D

Forum statistics

Threads
1,224,590
Messages
6,179,749
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top