Looping through cells

[Help-for-Heroes]

1. Hi, I'm trying to add a .wav sound to all cells in a column if it equals "! ALERT !". I have created a macro and added the following code:

Please Login or Register  to view this content.

This works for the first cell in the column and I'm assuming I'll to add a loop within it to apply the same to the rest of the column cells. I would also like to reference .wav files in other files but using c:\BERT\BEAM-ME-UP-SCOTTY doesn't work? How do I do this and is it something to do with the FILEPATH?

This is my first macro and I would appreciate help in achieving this, please help.

Many thanks

J

[AndyLitch]

Change your code to this and you won't need to loop.

Please Login or Register  to view this content.

To answer your second question - Nope you can play any wav you like - I've even got some AC/DC if you want it..

ARTE ET MARTE

[Help-for-Heroes]

Thanks, I'm getting a runtime error 438
Object doesn't this property or method

Any ideas??

Thanks

J

[AndyLitch]

Hit the debug when it errors and check which line in the code it takes you to... Highlighted in yellow.

[Help-for-Heroes]

Hi highlighted in yellow ( in italics) below...

If Target.comlumn <> 52 Then Exit Sub
If Target.Row < 4 Then Exit Sub
Threshold = "! ALERT !"
If Target.Value = Threshold Then PlayWAV

Thanks

J

[AndyLitch]

My fault - spelling mistake

should read............ If Target.column <> 52 Then Exit Sub

so now the code will only run when you click in column AZ rows 4 and above

[Help-for-Heroes]

Hi again. No error or sound now??

Thanks

J

[AndyLitch]

Hi
Would it be possible for you to upload a copy of the file so we can get a clearer understanding?.. It will speed up the process and is more likely to achieve the best possible solution.
Just be mindful of the fact that you're not supposed to upload sensitive or secret information

1. Select Edit Message or Reply
2. Select "Go Advanced" at the bottom right of the message area
3. Select the paper clip on the menu bar
4. Follow the wizard instructions for uploading a file.

[Help-for-Heroes]

Grrr, the upload window is not loading.

Is there anything else I can try to get it working?

Many thanks

J

[AndyLitch]

To be perfectly honest - I don't think sound is your best option..
How about highlighting non compliant cells in red automatically ?

How about something like this .

Error Detect.xlsm

[Help-for-Heroes]

Book1.xlsx

Attached now, I think1

[AndyLitch]

I'll never get that to work in a million years........ It's a picture

[AndyLitch]

OK put this code in the sheet3 code module

Please Login or Register  to view this content.