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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,160
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
 

Forum statistics

Threads
1,148,146
Messages
5,745,051
Members
423,917
Latest member
Frank1931

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