Sep 04 2019 04:23 PM
Hi everyone. I am trying, with no success, set an IF command, through VBA, to speak a pre set sentence if a pre defined condition is reached.
Example: =IF("A1>100";"Congratulation!! You reached your goal!";"")
The only thing I know I must use "application.speech.speak" as one of the commands in this VBA macro but I do not know how to build this program.
Sep 04 2019 05:53 PM
Does A1 contain any formula which gets calculated based on other cells?
If yes, you can use the Calculate Event to speak the predefined text. To do so, right click on Sheet Tab --> View code --> and paste the code given below into the opened code window.
Private Sub Worksheet_Calculate()
If Range("A1").Value > 100 Then
Application.Speech.Speak "Congratulation!! You reached your goal!"
End If
End Sub
If you manually change the cell A1, replace the above code with the Change Event code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "A1" Then
If Target > 100 Then
Application.Speech.Speak "Congratulation!! You reached your goal!", True
End If
End If
End Sub
The attached contains two sheets named "Calculate Event" and "Change Event" for you test the above codes.
If this is not what you are trying to achieve, please upload a sample workbook and explain that how do you want it to work.
Sep 04 2019 07:04 PM
Sep 04 2019 07:06 PM
SolutionNo problem, please test it as per your convenience and let me know if that works for you as desired.
Sep 04 2019 08:09 PM
@mctribeiro You asked the same question on another forum and I answered it there. Please don't ask the same thing in different places. You are wasting people's time.
Did you try my suggestion in the other forum? https://stackoverflow.com/questions/57779266/how-to-create-a-if-command-via-vba-to-sound-a-sentence/...
You never replied. If people take the time to solve your problems, it would be great to give them some feedback. Happy to help if you need more assistance, but you have to communicate.
Sep 04 2019 09:03 PM
Sep 04 2019 09:06 PM
Sep 04 2019 09:07 PM
@mctribeiro Sorry, but I didn't mean to be rude and I don't think I was. If you post the same question in different forums, somebody will work on your problem while someone else has already solved it. So that is indeed wasting people's time. Maybe you want to read this:
https://www.excelguru.ca/content.php?184
It explains things better that I did.
Sep 04 2019 09:20 PM
Sep 05 2019 03:14 AM
Sep 05 2019 03:20 AM
Sep 05 2019 06:31 AM - edited Sep 05 2019 06:35 AM
@Subodh_Tiwari_sktneerHi my friend. I tested your program this morning and it worked almost perfectly. The only problem was the voice started a loop and did not stop at all. I had to shut Excell (not only the specific sheet) to definitely quit the voice. I am attaching the sheet which will receive the voice command. I highlighted in yellow the column with the cells that will "produce" the voice. Please, let me know if I did something wrong. My best regards,
Marcelo
Sep 05 2019 07:47 AM
@Subodh_Tiwari_sktneerhere I come again. I found out when the loop happens. The original sheet, with some cells which numbers come from another program via DDE, Dinamic Data Exchange, in real time the voice starts an endless loop when the condition is reached but in the sheet I sent you, with frozen cells, your program works perfectly (exception: if one the cells used in the formula turns to something else but a number the VBA bugs).
Sep 05 2019 09:14 AM
The code I proposed was supposed to work for a single cell A1.
In your sample file, are you looping through the column U and want the code to check the formula output for multiple cells in column U and then speak the text once a specific output is returned by the formula in one or more cells?
Since this is the Calculate event, each time Sheet is recalculated the code will be triggered and check the target cell/cells for the output returned by the formula and if that meets the criteria, the code will invoke the speech method and to handle this scenario, you can place a flag once the text is spoken for a cell either by using a helper column and placing a flag in there or you can change the cell color so that the code can check before speaking the text if the cell color is not a specific color i.e. once the code speaks the text for a cell, change the cell color to say Red and the next time code will check the cell color and if it is red, it will skip and won't speak the text for the same cell again.
As far as the VBA errors are concerned when the value is not numeric, I think the code will produce an error if the cell has an error in it which you can easily check in the code with an IF condition like If Not IsError(Range("A1")) Then.
Sep 05 2019 11:00 AM
@Subodh_Tiwari_sktneermy idea is to write an specific sentence for each cell in column "U" as each line refers to a different information. How can write a formula that consider it or is it to complicate?
Sep 05 2019 11:39 AM
Honestly, in my opinion, forcing the macro to speak out several sentences in one go would be like overuse of this feature. Why not add some visuals on the sheet once one or more criteria are met?
Sep 05 2019 12:51 PM
Actually I thought it as well but a "voice alert" would work better. However I undertand your point. There is another solution do make the macro's work lighter: split my worksheet in several different sheets, each one with its own macro. Well, I do not know. Let me see how hard it would be to decide which way I will take. Anyway, I don't want to push you with this demand. Thanks a lot @Subodh_Tiwari_sktneer for your valuable help. I will keep trying.
Sep 07 2019 01:23 PM
Hi @Subodh_Tiwari_sktneer . As I am new in this forum how do I mark in my post it has been solved?
Sep 07 2019 07:41 PM
Your question gets marked as Solved once you confirm any post as a Best Response and you have already done that though you chose the wrong one as Best Response as that post didn't contain any solution.
If a post is helpful you can hit the Like button for that post but the post which resolved your question by offering a solution should be confirmed as the Best Response.
Once a question is marked as Solved, you will observe a green triangle with a while tick mark like below...
Btw, I am also new to this forum. :)
Aug 25 2021 07:50 AM
Sep 04 2019 07:06 PM
SolutionNo problem, please test it as per your convenience and let me know if that works for you as desired.