Automatically run VBA macro without a message box

Question

I have an Excel macro that I would like to run automatically when the file is opened. The only way I have gotten this to work is by adding a msgbox before calling to my subroutines. However, this requires me to click OK or close the box for the macros to run. I have tried using a timed msgbox sub, but this also does not work.

For some reason, the msgbox pops up before Excel is fully opened, at which point the macro gets stuck here (code for this is below). From here, I tried waiting for the file itself to be opened until it is in write-mode (Workbook.ReadOnly = false). This also did not work.

Public Sub msgBoxTimer()
    Const timeout = 2
    Dim objShell As Object
    Set objShell = CreateObject("WScript.Shell")
    objShell.Popup "Measurement will begin shortly", timeout
End Sub

Private Sub Workbook_Open()
    Call msgBoxTimer
    Call init           ' initiate device
    Call updateIndex    ' collect & record measurements
End Sub

What happens exactly when you don't call `msgBoxTimer` before calling `init` and `updateIndex` ? — DecimalTurn, Jul 09 '20 at 19:34
Looks like ```init``` and ```updateIndex``` also run before Excel is fully opened. The starting screen disappears once the subroutines are finished. So I guess my question now is how can I have the macro wait until Excel is fully opened, along with having the file fully loaded, before the macro starts? (Since msgbox is obsolete for what I need now, I’ll remove) I tried ```Application.Wait Now + waitTime``` but this still pauses the macro on the Excel starting screen window. — brhat, Jul 10 '20 at 20:59

DecimalTurn · Answer 1 · 2020-07-11T09:44:02.150

I get from your comment that you are probably running other shell commands in init and updateIndex.

What needs to be clear is that when you execute a Shell command via a Shell object in VBA, the default behavior is not to wait for the shell command to complete before running the next line of codes.

If you want Excel to wait for the Shell command to be completed before continuing, you can have a look at the answers to this question.

That being say, if you want Excel to be fully open before running any shell commands, you can use a MsgBox like you originally intended, but it has to be VBA's MsgBox that you would simply call like this:

MsgBox "Measurement will begin shortly"

The VBA thread will wait for the "OK" button to be pressed before continuing the execution.

Automatically run VBA macro without a message box

1 Answers1