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: 5

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top