In Visual Basic for Applications, is it possible to capture a program initializing and stop it, so it doesn't inturrupt YOUR code?

I’m no expert, but you can try “Doevents”

For example:

If a=b then
Doevents
End If

I’m not sure of what might happen with background opens or closes; but without it this should work. Did you try Deactivate event of the workbook? Because, when someone tries to open new or existing excel file, the current workbook gets deactivated.

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

When a macro is running no other events can be done. VBA does not really do background events. You need to use a VB6 add-in to run background events as its macros are built in to itself.

What you really need to ask is how to do the task that you want done. It doewn’t take hours to run a macro, usually only a second or two, depending on the task.