Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run Macro When Specific Text change

0

Hi

I have the excel sheet in which E ColumnShow "Buy" When C > D.

Whenever in range E1 to E20  Show Buy it should run the macro which i have Call Speak

I have attached the excel file for reference.

I have a code below

Sub worksheet_change(ByVal target As Range) Set target = Range("E1:E20") If target.Value = "Buy" Then Call Speak End If End sub  

Awaiting your reply

excel
posted by: Greedy on Fri Jul 30, 2021 at 7:51 am
Answer
Discuss

Answers

0

Greedy

Don't overwrite Target- it is set by the Worksheet_Change event (as with other events).

This code should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.CountLarge <> 1 Or Intersect(Target, Range("C1:D20")) Is Nothing Then Exit Sub

If Cells(Target.Row, 5) = "Buy" Then Call Speak

End Sub
This first line checks that only a single cell has been changed in range C1:D20 since these would lead to changes in E1:E20 (if not, it exits the sub).

Then second line checks if that value of the cell in column E is now "Buy" and if so calls your sub. The code in bold above means "the cell in the same row as the changed cell (in C or D) but column 5 (= column E)"

Note that you don't need to have a block If (If <<test>> then...(Else)... End If) in the case that a simple action is needed if the test is met (=True) and you don't need an actrion if it isn't.

Hope this fixes your problem.

posted by: John_Ru (rep: 6142) on Fri Jul 30, 2021 at 8:53 am
Discuss

Discussion

Greedy

Next time, please use the Code button and paste your VBA code within the tags (as I did in my answer- it helps others to copy/ test/ use your code). You can edit your original question to do that.
John_Ru (rep: 6142) Jul 30, '21 at 8:55 am
Consider using Target.CountLarge rather than the simple Count and Target.Cells.CountLarge for greater transparency.
This is because Count will crash the code if a large portion of the worksheet is changed, such as when an entire column is affected.
Target.Count/Target.CountLarge takes advantage of a rather funny default behaviour of the Count property. Of course, it isn't the Target that is counted but its cells and there really is no good reason for omitting that in the syntax. Quite the contrary. Only the other day I had a procedure where Target.Count crashed on me and I swore never to leave it out again.
Variatus (rep: 4889) Jul 30, '21 at 10:33 am
Thanks Variatus- I didn't know that but will change my Answer to correct for that.
John_Ru (rep: 6142) Jul 30, '21 at 10:52 am
Thanks for the reply.
when my formula in column E changes to buy the macro doesnot call automatically.When i enter the BUY manually then its run the macro. iwant to run the macro automacticaly  when fomula in column changes  to buy.
Please find attached sheet for reference.
Thanks in advance. Awaiting reply from you.

Private Sub Worksheet_Change(ByVal Target As Range)   If Target.Cells.CountLarge <> 1 Or Intersect(Target, Range("E1:E20")) Is Nothing Then Exit Sub   If Cells(Target.Row, 5) = "Buy" Then Call Speak   End Sub

my call macro
Sub Speak() Range("F1:F10").Speak Application.Speech.Speak txt End Sub        
Greedy Aug 2, '21 at 12:16 am
Greedy

Please EDIT your original question to attach a representative Excel file and to say how the Buy change occurs (e. g. via a formula linked to other cells /sheets). I'll take a look at it then.

Not sure why you put code at the end of your Discussion point above but next time please use the CODE button and enclose it between the tags. 
John_Ru (rep: 6142) Aug 2, '21 at 2:39 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login