VBA Excel宏消息框自动关闭 [英] VBA Excel macro message box auto close

问题描述

我有一个Excel宏，打开文件，打开另一个excel文件，刷新数据，保存然后关闭。

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

我也有一个中间位（Dim AckTime），显示一个弹出消息，以显示它的完成情况。

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

但是，由于我设置宏运行打开工作簿使用Public Sub Workbook_Open（）该消息框弹出，但不会在1秒后自动关闭。

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.

任何人都可以帮助？

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

推荐答案

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

应该是你唯一的错误，你没有设置等待时间。

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

这篇关于VBA Excel宏消息框自动关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！