SOLVED

Setting IF function to speak a sentence

Copper Contributor

 

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.

 

19 Replies

@mctribeiro 

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.

 

 

Hello my friend.
Thanks a lot for your quick response. The A1 cell is a result of other cells caculation, as your first assumption. I will test your command through your sheet example tomorrow and let you know the result. Thank you very much one more time.
best response confirmed by mctribeiro (Copper Contributor)
Solution

@mctribeiro 

No problem, please test it as per your convenience and let me know if that works for you as desired.

@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.

 

Hello.
Please, try to not be so rude in your comments. If you believe I am wasting your time simply ignore my question. It is very easy to judge people's actions and choices. And also remember: don't judge to avoid being judged. I hope you be well. Regards.
You are a very nice person. Thank you so much.

@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. 

@mctribeiro 

You're welcome! Glad it worked as desired.

 

Dear friend,
Be sure that, as soon as I solve this problem I will let people from both forum know. So far, nobody got the solution. And if you check, I posted my situatuon at the same time, considering that I will reach different people in two different forums, increasing the chance for a quick answer. Anyway, try to be more polite next time you take your precious time to help other people otherwise is better if you do something else. Please, do not take it as offense but as a suggestion from someone probably older than you. Regards.
Regards
By the way, I read the article from that link. I completely understand your point and will follow some given tips. Thank you.

@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

@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).

@mctribeiro 

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.

@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?

@mctribeiro 

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?

@Subodh_Tiwari_sktneer 

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.

Hi @Subodh_Tiwari_sktneer . As I am new in this forum how do I mark in my post it has been solved?

@mctribeiro 

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...

Solved.jpg

 

Btw, I am also new to this forum. :)

Hi Sir,

I found that you have described how to use if function with speak cell I did it but one problem arises. Problem is the other cells are also speaking the same command even when the other cells value are different . Is it possible for you to resolve this problem as I want other cell not to speak.

Regards,

Debjit
1 best response

Accepted Solutions
best response confirmed by mctribeiro (Copper Contributor)
Solution

@mctribeiro 

No problem, please test it as per your convenience and let me know if that works for you as desired.

View solution in original post