Target Column & Row question

[amx]

I am trying to have any cell that in any row in any column (based upon code address change) that would Speak a word if the number is Under <10 and play a sound (wav) if the number is over >10.
This code works fine for all of column A, but on column B it uses the Speech and Sound from column C.
Thank you for your help.

Private Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim strSound As String
    Dim strtalk As String
 
    If Not Intersect(Target, Range("$A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$B$2,$C$2")) Is Nothing Then
 
    If Target.Column = 2 Or Target.Column = 3 Then
    strSound = Choose(Target.Row, "Drumroll", "beep")
    strtalk = Choose(Target.Row, "house and horn", "Dog  and  cat")
 
  Else
    strSound = Choose(Target.Row, "tada", "ding", "tada", "beep", "ding", "beep")
    strtalk = Choose(Target.Row, "Dog", "Cat", "Pig", "House", "cat", "Rat")
  End If
 
 
  If Target.Value > 10 Then
    sndPlaySound "C:\WINDOWS\Media\" & strSound, 0
  End If
  If Target.Value < 10 Then
    Application.Speech.Speak strtalk, 0
  End If
End If
 
End Sub
 

[Killer42]

I am trying to have any cell that in any row in any column (based upon code address change) that would Speak a word if the number is Under <10 and play a sound (wav) if the number is over >10.
This code works fine for all of column A, but on column B it uses the Speech and Sound from column C.

Private Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim strSound As String
    Dim strtalk As String
 
    If Not Intersect(Target, Range("$A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$B$2,$C$2")) Is Nothing Then
      If Target.Column = 2 Or Target.Column = 3 Then
        strSound = Choose(Target.Row, "Drumroll", "beep")
        strtalk = Choose(Target.Row, "house and horn", "Dog  and  cat")
      Else
        strSound = Choose(Target.Row, "tada", "ding", "tada", "beep", "ding", "beep")
        strtalk = Choose(Target.Row, "Dog", "Cat", "Pig", "House", "cat", "Rat")
      End If
      If Target.Value > 10 Then
        sndPlaySound "C:\WINDOWS\Media\" & strSound, 0
      End If
      If Target.Value < 10 Then
        Application.Speech.Speak strtalk, 0
      End If
    End If
End Sub
 

(I've modified the indenting of the code for clarity - at least in I find it clearer, anyway. :))

I'm not familiar with the Intersect function, so may be way off-track here. But surely this line

If Target.Column = 2 Or Target.Column = 3 Then

instructs the system to treat columns B and C in the same way?

[amx]

(I've modified the indenting of the code for clarity - at least in I find it clearer, anyway. :))

I'm not familiar with the Intersect function, so may be way off-track here. But surely this line

If Target.Column = 2 Or Target.Column = 3 Then

instructs the system to treat columns B and C in the same way?

Thank you for your help.
Do you have any ideas on how to make this work.

[Killer42]

Thank you for your help.
Do you have any ideas on how to make this work.

I don't think I understand well enough exactly what is supposed to happen. I mean, I follow the basic if_blah_play_sound_otherwise_speak thing, but not exactly where all this is supposed to happen, and where it isn't.

As I said, I'm not familiar with Intersect( ) but it looks as though you are applying some sort of "business rules" on where you will perform the various actions. Just need some more detail on the rules behind the code.

[amx]

Killer42 - Thank you for your help.
I am just trying to see if I can get this concept to work. I am trying to have any cell or any amount of cells on a worksheet make a "Sound" from the result of a formula is less then 10 (or if any letter "A") or have it speak a word if the same cell has a number from the results of a formula.
This would do the same thing as "Conditional Formatting" does with colors or fonts. This would do it with Wav Sounds and Speech.
I was useing >10 & <10 or "A" as a example.
That is what I am trying to do. How would you code this concept if you were doing it.
Thank you for your help.

[Killer42]

Killer42 - Thank you for your help.
I am just trying to see if I can get this concept to work. I am trying to have any cell or any amount of cells on a worksheet make a "Sound" from the result of a formula is less then 10 (or if any letter "A") or have it speak a word if the same cell has a number from the results of a formula.
This would do the same thing as "Conditional Formatting" does with colors or fonts. This would do it with Wav Sounds and Speech.
I was useing >10 & <10 or "A" as a example.
That is what I am trying to do. How would you code this concept if you were doing it.
Thank you for your help.

I'm afraid I'm still having a little trouble understanding your intentions. Do you want the same thing to apply to everything on the sheet? Or do you wan to be able to somehow set this to happen for particular area, as with conditional formatting? The latter would be very simple, as you can just apply the value test to everything. For the latter, I'm not sure how you would go about identifying which ranges it applies to.

To refer back to your original code, I think my confusion really stems from the highlighted lines.

Private Declare Function sndPlaySound Lib "winmm.dll" _
  Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
  ByVal uFlags As Long) As Long
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim strSound As String
  Dim strtalk As String
 
   If Not Intersect(Target, Range("$A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$B$2,$C$2")) Is Nothing Then
    If Target.Column = 2 Or Target.Column = 3 Then
       strSound = Choose(Target.Row, "Drumroll", "beep")
      strtalk = Choose(Target.Row, "house and horn", "Dog  and  cat")
    Else
      strSound = Choose(Target.Row, "tada", "ding", "tada", "beep", "ding", "beep")
      strtalk = Choose(Target.Row, "Dog", "Cat", "Pig", "House", "cat", "Rat")
    End If
    If Target.Value > 10 Then
      sndPlaySound "C:\WINDOWS\Media\" & strSound, 0
    End If
    If Target.Value < 10 Then
      Application.Speech.Speak strtalk, 0
    End If
  End If
End Sub
 

Is this intended to set different sounds per row, or what?

[amx]

Killer 42 - Thank you for your help.
I am trying to have as a example
Cell A1 <10 "wav tada" >10 "speak - dog"
Cell A3 <10 "wav ding" >10 "speak - cat"
Cell A6 <5 "wav drumroll" > 5 "speak - pig"

Cell B4 ="A" "wav beep" = "B" speak - house"

Cell C2 = <100 "wav tada" >100 "speak - horse"

Answers to your questions -
Q - Do you want the same thing to apply to everything on the sheet?
A - Only the cells that I choose (Example A1, A3, A6, B4, C2)

Q - do you want to be able to somehow set this to happen for particular area, as with conditional formatting
A - Yes only the cells that I choose just like conditional formatting

Q - I'm not sure how you would go about identifying which ranges it applies to.
A - That is the same problem that I am having on setting a different conditions
to cells that are in different place in a sheet and not in a range all togeather.

Q - I think my confusion really stems from the highlighted lines.
A - The highlighted area did not show up?


Q - Is this intended to set different sounds per row, or what?
A - It is intened to have different sounds and speech in each cell
the sounds and speech is different in every cell and every row
A sound for one condition and speech for another condition in the same cell
and each cell would stand on it's own.

I just wonder if Excel can do this. This would be a fancy conditional formatting

Thank you for your help.
I have been working on this for a while.

[Killer42]

Killer 42 - Thank you for your help.
I am trying to have as a example
Cell A1 <10 "wav tada" >10 "speak - dog"
Cell A3 <10 "wav ding" >10 "speak - cat"
Cell A6 <5 "wav drumroll" > 5 "speak - pig"

Cell B4 ="A" "wav beep" = "B" speak - house"

Cell C2 = <100 "wav tada" >100 "speak - horse"

Answers to your questions -
Q - Do you want the same thing to apply to everything on the sheet?
A - Only the cells that I choose (Example A1, A3, A6, B4, C2)

Q - do you want to be able to somehow set this to happen for particular area, as with conditional formatting
A - Yes only the cells that I choose just like conditional formatting

Q - I'm not sure how you would go about identifying which ranges it applies to.
A - That is the same problem that I am having on setting a different conditions
to cells that are in different place in a sheet and not in a range all togeather.

I don't know how you would tell Excel which sounds apply to which cells. Perhaps you could do something like setting a specific background or border colour for the cells to be targetted. And you could have another sheet on which you set up the relationship between colours and sounds.

Q - I think my confusion really stems from the highlighted lines.
A - The highlighted area did not show up?

Well, either you didn't follow what I meant, or your browser doesn't display these messages properly. Because in the Code box which followed that statement, some of the lines are shown in bold - this is what I meant when I said "highlighted lines".

Q - Is this intended to set different sounds per row, or what?
A - It is intened to have different sounds and speech in each cell
the sounds and speech is different in every cell and every row
A sound for one condition and speech for another condition in the same cell
and each cell would stand on it's own.
I just wonder if Excel can do this. This would be a fancy conditional formatting

I'm sure it can be done, in some way. As I said, it's just a matter of figuring out how to set up the relationships between locations and sounds. Using visible attributes may be the simplest, but may not be what you're looking for (for one thing, it may totally mess up your display). But it would allow you to fairly easily apply rules in your VBA code like "If cell has green background, play this sound".

I'm actually on holidays at the moment, so I might spend an hour or two (tomorrow, as it's almost time to finish up on the computer for the day) and see what I can come up with. I'm confident we can get something working fairly quickly, then you can play with it to your heart's content.

[amx]

Killer 42 - Thank you for your help.

I am still working on it also but with no luck.
I look foward to seeing what you might come up with.
I was not sure if Excel could do something like this.
Have a nice holiday. Thank you for taking time out of your holiday to help me.
Where are you having your holiday at?

[Killer42]

Killer 42 - Thank you for your help.

I am still working on it also but with no luck.
I look foward to seeing what you might come up with.
I was not sure if Excel could do something like this.
Have a nice holiday. Thank you for taking time out of your holiday to help me.
Where are you having your holiday at?

Well, I finally found some time to work on the pseudo-conditional-formatting idea. What I’ve come up with so far is certainly not a piece of work I’d be proud to display, but may provide some ideas, at any rate.

I’ll try to work out how (or if) I can attach it to this post, or a PM. Otherwise, I’ll try to get it to you via e-mail or something. Anyway…


Ok, the idea here is that you have your worksheet with your data on it, or where the user works. There is a corresponding worksheet which I’ve called “Flags” in my case, which the user should not touch (if you can hide it, all the better probably).

The user selects their cell (or range of cells) on the data sheet. One or more keypresses are set up to trigger a macro (or a bunch of macros), which sets the value of the cell(s) on the Flags sheet, in the locations corresponding to the selected cells. In other words, if you selected cells A1 to B3 then triggered the Set_A macro, it would place “A” in the Flags sheet, in cells A1, A2, A3, B1, B2, and B3.

In the Change event for the Data sheet, you invoke the ApplyRules macro, which for each of the specified cells will:

• look up the value at the corresponding location in the Flags sheet
• Find that value in the list of rules in column A on the Rules sheet
• Paste the cell address of the user-entered value in the corresponding cell in column C on the Rules sheet
• Paste the user-entered value in the corresponding cell in column B on the Rules sheet.

Your actual rules, to play the different sound files and so on, can then be applied to this small range of cells in the Rules sheet. The whole point of all the rest of this stuff is just to copy values to this point so the rules can be applied to them. (I couldn't get to TheScripts today to refresh my memory on what sort of rules you were applying. Oh well...)

I hope some of this makes sense.

To try it out, just select some cells in the Data sheet and run one of the “Set_” macros to set a rule (A, B, C or D) there. Have a look at the Flags sheet to see the effect. Obviously, you could get the same result by typing the A, B or whatever anywhere on the Flags sheet, too. But the idea is to have a key or a button for the end user to do the action.

Then enter some values in the Data sheet – preferably at least one in and one not in the cells you “set”. It should produce a message box showing what rule would be applied to the value you entered – including a default rule if you haven’t set one for that cell.

P.S. Well, I've just managed to get into TheScripts, pasted this message (written hours ago in Word) into a reply, and found that there's no facility for an attachment. Will have to work something out.

Ahah! When I went back to edit the message, I was able to add the attachment. :D

[amx]

Killer 42 - Thank you for all of your work.
I am trying to make your code work and I found out that I am not very smart.
I have not been able to get it to work.
Would it be possible for you to please make up a working sheet with quite a few examples so I could understand what to do.
I understand your concept but I just get lost in the middle of setting it up.
Thank you for all of your hard work and knowledge.

[Killer42]

Killer 42 - Thank you for all of your work.
I am trying to make your code work and I found out that I am not very smart.
I have not been able to get it to work.
Would it be possible for you to please make up a working sheet with quite a few examples so I could understand what to do.
I understand your concept but I just get lost in the middle of setting it up.
Thank you for all of your hard work and knowledge.

No problem. My work was probably a bit confusing anyway. There's no reason to think you're dumb, just because you don't think the same way I do (few people do :)).

I should be able to get back to this in a couple of hours, so I'll try to create a couple of sample forms based on your speak/play rules.

Note that the main step which I haven't performed is to attach the Set_* macros to a key or button. I'm rather rusty in that area.

[amx]

Killer 42 - Thank you for your help.
I am still working on trying to make it work.

[Killer42]

Killer 42 - Thank you for your help.
I am still working on trying to make it work.

Sorry about the delay - been pretty busy.

Here's a quick sample. I just filled in rules A, B, C and D to play different sounds or speak different things (based on your original post) depending on whether a value entered on the Data sheet is over 10 or not. Try entering some values into the coloured cells, which are the ones I applied the rules to.

(In my case, the speech didn't work. I'd guess that's because I either have the wrong Excel version, don't have the speech set up, or haven't added a reference in the project. I'll leave that part to you, since I believe you already have that part working.)

To set or change the rule for a cell or range, you select it and run the appropriate macro (Set_A, Set_B etc).

[amx]

Killer 42 - Thank you for all of your knowledge and hard coding work.
I have look over the code and that is very brillant and complex.
Your knowledge is very impressive. The sounds works on the sheet.
I will work on trying to make it work on different rows and columns and
see how much it can do.
Thank you very much for your help.
I hope you had a relax and enjoyable holiday.

[Killer42]

Killer 42 - Thank you for all of your knowledge and hard coding work.
I have look over the code and that is very brillant and complex.
Your knowledge is very impressive. The sounds works on the sheet.
I will work on trying to make it work on different rows and columns and
see how much it can do.
Thank you very much for your help.

Have fun!

The way to set the sounds for a row or column (or any range) is to put the "A" or "B" etc. in that range in the "Flags" sheet. You can do that directly on the Flags sheet, but the idea is to select the range in the Data sheet and run one of the Set_ macros to do it. Ideally, these macros should be assigned to buttons or something.

It would also be nice, I suppose, if you could see what rule is applied to a cell when you select it, but I didn't work on that part.

Anyway, I hope this gives you some ideas to work with.

I hope you had a relax and enjoyable holiday.

I still have another three weeks of holidays to go. :D