Automatic Macro Trigger - Macro runs multiple times

[ryandonovan22]

Hello,

I am trying to get my 'TidyAll' Macro to run when a cell is changed and I am using the following code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("H5")) Is Nothing Then TidyAll
End Sub

It is working, although for some reason the macro being called up runs 3 times then stops?

What am I doing wrong?

FYI - The Macro is stored in a module.

Thanks,
Ryan.

 

[JoeMo]

Hello,

I am trying to get my 'TidyAll' Macro to run when a cell is changed and I am using the following code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("H5")) Is Nothing Then TidyAll
End Sub

It is working, although for some reason the macro being called up runs 3 times then stops?

What am I doing wrong?

FYI - The Macro is stored in a module.

Thanks,
Ryan.

Is the macro installed as sheet code for the sheet you want to monitor for changes? Does the TidyAll routine make changes to that sheet?

 

[ryandonovan22]

Is the macro installed as sheet code for the sheet you want to monitor for changes? Does the TidyAll routine make changes to that sheet?

To the first question, Yes, I believe so (it auto runs when data is added)

secondly the TidyAll routine adds columns, deletes columns, deletes rows, calculates stuff, and generally cleans up the look, oh a VLOOKUP too.

 

[JoeMo]

To the first question, Yes, I believe so (it auto runs when data is added)

secondly the TidyAll routine adds columns, deletes columns, deletes rows, calculates stuff, and generally cleans up the look, oh a VLOOKUP too.

If your doing that on the sheet that you are monitoring, I'm surprised, TidyAll runs only 3 times as opposed to an infinite loop. The addition/deletion of cells are change events which will continuously trigger TidyAll.

Try adding this at the start of the worksheet_change code:

Application.EnableEvents = False

and this just before the end sub line:

Application.EnableEvents = True

 

[ryandonovan22]

If your doing that on the sheet that you are monitoring, I'm surprised, TidyAll runs only 3 times as opposed to an infinite loop. The addition/deletion of cells are change events which will continuously trigger TidyAll.

Try adding this at the start of the worksheet_change code:

Application.EnableEvents = False

and this just before the end sub line:

Application.EnableEvents = True

Seems to have worked perfectly.

Thanks for your help.

 

[ryandonovan22]

Seems to have worked perfectly.

Thanks for your help.

Urm... I have tried it again and it isn't now.

Should both of those lines of VBA be in the PrivateSub, or should one be in the PrivateSub and one in the TidyAll Macro

 

[JoeMo]

Seems to have worked perfectly.

Thanks for your help.

You are welcome - thanks for the reply.

 

[ryandonovan22]

You are welcome - thanks for the reply.

No worries, JoeMo.

Just a heads up, it is working fine. For some reason Excel just stopped running the macro but I think it was just excel being excel and when I closed and reopened all of the files it worked fine.

Ryan

 

[JoeMo]

No worries, JoeMo.

Just a heads up, it is working fine. For some reason Excel just stopped running the macro but I think it was just excel being excel and when I closed and reopened all of the files it worked fine.

Ryan

Possibly you ran it through the line that disables events but it never got to the line at the end that enables them again. Or, maybe you moved the line that enables events to your other sub. If that should happen again, open the VBE window, press ctrl + g to open the Immediate Window, type "Application.EnableEvents = True" (w/o the quote marks) and press Enter. That turns events back on.