Macro Triggering Spontaneously

[Orionator]

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

 

[Norie]

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]

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]

What does the code do?

 

[Orionator]

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

MsgBox "Address F1 (Monthly) triggered update pivs", vbOKOnly

For instance

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]

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]

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]

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.

 

[Fazza]

via google,
http://access-excel.tips/excel-vba-declare-global-variable/
https://www.mrexcel.com/forum/excel-questions/425695-global-vs-public-variable.html

depending on how it is set up, likely is that the data connections can have a basic setting to refresh every n minutes. you may be able to find that, and also when the query last refreshed - maybe you can check properties via VBA or the VBE's immediate window