Workbook open event not working

[dp2309]

My code is fine. It runs when I press F5 in the VBA developer window. And i checked the code resides in the Workbook only.

But the message box doesn't execute when I open the excel file. What could I be doing wrong?


Private Sub Workbook_Open()
If Weekday(Now) = vbThursday Then
Msg = "Today is Thursday, make sure that you "
Msg = Msg & "submit the TPS report"
MsgBox Msg
End If
End Sub

[Bob Phillips]

Did you put that code in the ThisWorkbook code module?

As today is Friday, nothing will happen that informs you it has run.

[dp2309]

Apologies. Should have been clearer. I tried this yesterday on Thursday. Like I said it worked when hit F5

[Bob Phillips]

Is it in ThisWorkbook?

[Paul_Hossler]

Apologies. Should have been clearer. I tried this yesterday on Thursday. Like I said it worked when hit F5

Usually means (as Bob said) that you put the code into the wrong module


27020


Although sometimes, I've had Application.EnableEvents = False when messing with a bunch of open workbooks.

The setting is Application level. All workbooks opened within any single instance of the Excel application will have their events disabled.

[dp2309]

27027I placed the code in the workbook. Still same result. Nothing happens when I open the workbook

[SamT]

Try commenting out the If and End If lines.

[dp2309]

Tried that. Same problem. And application.enableevents is true

[p45cal]

Try putting a Stop instruction as the first line of the code. When you open the file the first thing (as long as Macros are enabled) that happens is that the vbe opens with the Stop instruction highlighted in yellow. If it does not do this then the event is not firing.

[Paul_Hossler]

Another thing to try



Private Sub Workbook_Open()
MsgBox Application.EnableEvents
End Sub

If that doesn't show anything, then maybe strip out every macro and the data that is not needed from the workbook and post it here if it shows the same behavior so that someone else can see if they have the issue

[dp2309]

This worked but I had to remove all macros in the workbook

[Paul_Hossler]

My guess is that there is some sort of interaction going on

Does the project compile error free?

Can you post the entire original workbook?

[dp2309]

2703327034Uploaded entire code in workbook

[p45cal]

Uploaded entire code in workbookWhere?

[Paul_Hossler]

A screen shot is no good if you want help

Use [Go Advanced] and then the paperclip icon to attach a workbook that people can test