When Application.Interactive = False, I want to prevent the active window from switching arbitrarily when displaying a message box.

Marino_Y 41 Reputation points
2022-11-25T07:06:59.38+00:00

I am using translation software. Sorry for any mistakes.
When executing the code below, it switches from Excel to another window (Explorer, Chrome, etc.) without permission before the message box is displayed.
I think that it is easy to wake up immediately after opening another window and returning to Excel with Alt + Tab.
How can I prevent the active window from switching while controlling input and screen transitions? If anyone knows a solution, I would appreciate it if you could let me know.

Below is what I have tried.

  1. Commenting out “Application.Interactive = False”.
  2. Put “DoEvents” before “Application.Interactive = False, True” and “MsgBox "ERROR!!".”
  3. Put a waiting time with “sleep” before displaying the message box.
    Only “1” was effective, but I am looking for other methods because it will be in a state where the user can operate. Sub TEST() 
     Application.Interactive = False
    ' Transition to “RUNNING” sheet'
    Workbooks("TEST.xlsm").Activate
    Worksheets("RUNNING").Activate 
     Application.ScreenUpdating = False
    ' Start connection confirmation batch “ping.bat”'
    TaskID = Shell("c:\test\ping.bat", 2)
    hProc = OpenProcess(PROCESS_ALL_ACCESS, False, TaskID)
    If OpenProcess(PROCESS_ALL_ACCESS, False, TaskID) <> vbNull Then
    Call WaitForSingleObject(hProc, INFINITE)
    CloseHandle hProc
    End If ' If you can’t connect, transition to “ERROR” sheet'
    FILECHK = Dir("c:\test\PINGERR.txt", 0)
    DoEvents
    If FILECHK = "PINGERR.txt" Then
    Workbooks("TEST.xlsm").Activate
    Worksheets("ERROR").Activate
    MsgBox "ERROR!!" '←I would like to avoid switching here.'
    DoEvents
    Application.ScreenUpdating = True
    Application.Interactive = True
    End
    Else
    MsgBox "Nomarl End!" '←I would like to avoid switching here too.'
    End If ' If you can connect, transition to “OK” sheet'
    Workbooks("TEST.xlsm").Activate
    Worksheets("OK").Activate 
     Application.ScreenUpdating = True  
 Application.Interactive = True
    End Sub
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,530 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Marino_Y 41 Reputation points
    2022-11-30T06:35:16.003+00:00

    When calling the command prompt with the shell function, the screen switching no longer occurs by calling it with 0 (vbHide).
    However, we had to be careful because we could not take action when the command prompt did not exit.
    Sheet protection is also effective if you just want to prevent users from typing.

    0 comments