You can trap new workbook event. This event is already available to the application object. I don’t know what your code is, but I will use a sample example to illustrate this. You may then use it accordingly.
However, your code must be in one of class modules, i.e. any sheets, ThisWorkbook or your custom class module. This will not work in a standard module. I have used ThisWorkbook module in the example below.
The example puts numbers 1 to 100 into cells of first column starting from 1st cell of 1st row and pauses for 1 second between each step. So, if you run this procedure and try to open a new workbook while the code is running, a message box will appear. You can type in your alert message in this message box. The running program, Example in this case, is then stopped by End statement.
Declare App with “WithEvents” as application on top. Hit enter. You will now be able to select App from the left drop-down of the code window. Select it and from the right drop-down select “NewWorkbook” event. VBA will insert proper declarations. Type in your alert message code here, i.e. message box.
Within your procedure, just set App to Application. This should now work.
My example code, as in ThisWorkbook module, is as below.
Option Explicit
Dim WithEvents App As Application
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox “Hey! You are about to open a new workbook!”
End
End Sub
Public Sub Example()
Dim i As Integer, start As Double
Set App = Application
For i = 1 To 100
Cells(i, 1).Value = i
start = Timer
Do While Timer < start + 1
DoEvents
Loop
Next i
End Sub