Macro call going into loop

casualexceler

New Member
Joined
Sep 7, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have a table where one column is the "completed" column.
When a task is completed, there is a drop down menu in the "completed" column with the "yes" option which the user can then select. This will then call a macro which I have written that will remove the row and paste it into a different worksheet to keep all the completed tasks in another worksheet.

I tried to write a code so that if any cells in from i4 to i400 has "yes" inputted via the dropdown menu it calls and executes the macro I've called "yes"

The following code loops between itself and the "yes" macro

Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range("i4:i400")
If Target.Value = "yes" Then
Call yes
End If


End Sub

essentially once the macro runs, and completes it runs the above code again.
There is conditional formatting on the cell also - would this be triggering the loop?
 

Attachments

  • Capture.PNG
    Capture.PNG
    16.6 KB · Views: 3

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,273
Office Version
  1. 2010
Platform
  1. Windows
You say your code "removes" the row, this is a worksheet change so it triggers again, thus the loop. You need to set at application.enableevents to false before a reset it after. Not at pc at the moment so no code
 

casualexceler

New Member
Joined
Sep 7, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
You say your code "removes" the row, this is a worksheet change so it triggers again, thus the loop. You need to set at application.enableevents to false before a reset it after. Not at pc at the moment so no code
Spot on, thank you! I used the following link to build on you suggestion
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,777
Messages
5,766,411
Members
425,352
Latest member
Jack3h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top