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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,280
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,969
Messages
5,767,382
Members
425,410
Latest member
SmittyT

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