Excel实战技巧71： 自动响应消息框信息

Public Declare Function SetTimer& Lib "user32" (ByValhwnd&, _
    ByVal nIDEvent&,ByVal uElapse&, ByVal lpTimerFunc&)
Private Declare Function KillTimer& Lib "user32"(ByVal hwnd&, _
    ByVal nIDEvent&)
Public Const NV_INPUTBOX As Long = &H5000
 
Public Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, ByValidEvent As Long, ByVal dwTime As Long)
    SendKeys "%Y"
    KillTimer hwnd, idEvent
End Sub
 
Sub test()
    With Application
        .Calculation =xlCalculationManual
        .ScreenUpdating =False
    End With
 
    Dim targetworkbook AsWorkbook
 
    Dim usersave As VbMsgBoxResult
 
    Set targetworkbook =Workbooks.Open("C:\test.xlsm", UpdateLinks:=0)
 
    Calculate
    targetworkbook.Activate
    SetTimer 0, NV_INPUTBOX,1000, AddressOf TimerProc
    Application.Runtargetworkbook.Name & "!tester"
 
    targetworkbook.Activate
 
    With Application
        .Calculation =xlCalculationAutomatic
        .ScreenUpdating =True
    End With
 
End Sub

Sub tester()
    TimedMsgBox
End Sub
 
Sub TimedMsgBox()
    Dim cTime As Long
    Dim WSH As Object
 
    Set WSH =CreateObject("WScript.Shell")
    cTime = 5 '5 秒
    Select CaseWSH.Popup("打开Excel文件?!", cTime, "询问", vbOKCancel)
        Case vbOK
            MsgBox "你单击了确定"
        Case vbCancel
            MsgBox "你单击了取消"
        Case -1
            MsgBox "超时"
    End Select
End Sub