VBA Excel macro message box auto close

Question

I've an Excel macro that runs on opening the file, opens another excel file, refreshes the data, saves and then closes it.

I also have a middle bit (Dim AckTime) that displays a pop up message for second to show what it's done.

BUT.. Since I set the macro to run on opening the workbook using Public Sub Workbook_Open() the message box pops up but will not close automatically on 1 second anymore.

Can anyone help?

Public Sub Workbook_Open()

Application.ScreenUpdating = False Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

     With Workbooks.Open("\\filename.xlsm")
         ActiveWorkbook.RefreshAll 'updates the data
         ActiveWorkbook.Sheets("Dashboard").Range("A2").Value = DateTime.Now 
         ' updates this cell with the current time

         Dim AckTime As Integer, InfoBoxWebSearches As Object
         Set InfoBoxWebSearches = CreateObject("WScript.Shell")
         AckTime = 1
         Select Case InfoBoxWebSearches.Popup("Updated, saving & closing...", _
             Case 1, -1
         End Select

         .Save
         .Saved = True 'when saved..
         .Close 0 'close the file

     End With

End Sub

CreateObject("WScript.Shell").Popup "your message. .", 1, "message header" ' pop up that closes in 1 second — Mike
Does this still work? Microsoft have made a few changes to the ways that one process can close a message box launched by another: I posted a long explanation here — Nigel Heffernan

Joe Laviano Joe Laviano · Accepted Answer · 2013-09-25T14:57:38

Select Case InfoBoxWebSearches.Popup("Updated, saving & closing...", AckTime)

Should be your only error. You just didn't set the wait time.

VBA Excel macro message box auto close

1 Answers