Macro Triggering Spontaneously

Orionator

New Member
Joined
Dec 29, 2017
Messages
14
I have a workbook that I created from scratch that has a macro that is triggering at complete random it seems. I've tried to find what makes it trigger to repeat it to no avail. Is there any way (aside from Ctrl+Break) to see what macro is running or has just finished running without adding some msgbox at the end of each of my macros?

So far I've checked each of the auto-triggered events that I have for specific sheets but the troublesome macro seems to activate regardless of what sheet I'm on. If anyone has any suggestions, I'm all ears. This macro runs fine on my machine but it does crash other machines.

Thank you,

Orion
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Do you know which macro it is?

If you do you can put a breakpoint on it with F9 and whenever it runs code execution will be halted.

When execution is halted you can try investigating why/how it was triggered, for example you can have a look at the Call Stack.
 

Orionator

New Member
Joined
Dec 29, 2017
Messages
14
I don't know which one it is. I'm kind of thinking of putting an OKOnly msgbox at the beginning of each macro just to catch it.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
What does the code do?
 

Orionator

New Member
Joined
Dec 29, 2017
Messages
14

ADVERTISEMENT

Don't know, seemingly nothing. Probably refreshes some pivot table. I have about 12 macros and about 7 triggered events. Some of the events call these macros, some of them just update a pivot table.

I just went through each code and inserted a
Code:
MsgBox "Address F1 (Monthly) triggered update pivs", vbOKOnly

For instance

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$1" Then
Call Dept_Update_Weekly
End If


If Target.Address = "$D$1" Then
Call Line_Update_Weekly
End If


If Target.Address = "$F$1" Then
MsgBox "Address F1 (Weekly) triggered update pivs", vbOKOnly
ActiveSheet.PivotTables("PivotTable5").PivotFields("Week Number").CurrentPage = Range("F1").Value
Call Line_Update_Weekly
End If


End Sub
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
the 'for instance' code is a worksheet_change code

consider if you need "Application.EnableEvents = False" added near the start of the code, and then near the end "Application.EnableEvents = True"

This to stop cascading calling of event code/s. Without this sort of switch to turn off events, a worksheet change code can itself trigger events codes & start a cascade of event codes. Stepping through code can expose such activity.

Another thought, create a global boolean constant to control debug printing. add a debug print line - instead of the msgbox - at the start of every sub/function something like, basic idea,
If gbWANT_DEBUG_PRINT Then debug.print "whatever"

So set the boolean to True while you're debuggging & False when printing not wanted. Monitor the VBE's immediate window to see what happens.
 

Orionator

New Member
Joined
Dec 29, 2017
Messages
14

ADVERTISEMENT

the 'for instance' code is a worksheet_change code

consider if you need "Application.EnableEvents = False" added near the start of the code, and then near the end "Application.EnableEvents = True"

This to stop cascading calling of event code/s. Without this sort of switch to turn off events, a worksheet change code can itself trigger events codes & start a cascade of event codes. Stepping through code can expose such activity.

Another thought, create a global boolean constant to control debug printing. add a debug print line - instead of the msgbox - at the start of every sub/function something like, basic idea,
If gbWANT_DEBUG_PRINT Then debug.print "whatever"

So set the boolean to True while you're debuggging & False when printing not wanted. Monitor the VBE's immediate window to see what happens.

Fazza,

Thank you! I love the idea of the debug print. After playing in the workbook for about 10 minutes, triggering the macros with a msgbox is getting very annoying... I haven't set anything up that is global before, can you point me in the right direction? Where does that code exist.

I am very familiar with the Application.EnableEvents command. I appreciate what you are saying about disabling and enabling them on the worksheet changes, but I need them to stay on as the first called macro will change a cell that calls the second macro on purpose. We have multiple production zones that have separate lines. Once I change the zone, the line automatically changes to the first line that is a part of that zone (i.e. the first macro) but when the line changes, I need all pivot table information to update (the second macro). But, when I am only changing the line, I need to also only update the pivot info. I know I could have accomplished this by adding the call to the end of the first macro as it will run every time it is used but at that point I would have had to use the Application.EnableEvents = True/False so that I wouldn't trigger the second worksheet change macro.

Orion
 

Orionator

New Member
Joined
Dec 29, 2017
Messages
14
Ok so this is not a macro doing this. I just had it happen and no msgbox popped up.

Symptoms:
-all excel windows lock up
-no flickering or flashing (common with macros that don't use the application.screenupdating control)

The only other thing I have utilized in this workbook that I haven't in others is data connections. We use an OLEDB for our production so I tap into that using the data connections. Has anyone noticed random refreshes that halt all other operations? I can't say for sure if this is exactly what the issue is, but it is the only thing that I'm not used to using in this workbook.
 

Forum statistics

Threads
1,148,294
Messages
5,745,936
Members
423,985
Latest member
sayed manzar

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