Click here to Skip to main content
15,908,274 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

VBA
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.'
    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


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.
Posted
Updated 25-Nov-22 5:46am
Comments
Member 15627495 25-Nov-22 2:25am    
by using another Thread, you can stay on your main window.

when you call shell it goes straight to it. but by embeding in a secondary thread you will keep the hand on your main windows.
Marino_Y0301 28-Nov-22 23:28pm    
Thank you for your comment.
I forgot to mention the environment.
I am using excel 2019 on windows10.
So I can't test your answer, sorry.

Nothing you do is going to prevent the "switching to another window without permission". There is no such thing as getting permission to switch windows, especially since your code is the one that is launching a new process that will show up in a CMD Prompt window.

Look at your Shell statement. The value 2 (vbMinimizedFocus) tells Shell to launch the process minimized WITH FOCUS, which will give the input focus to the minimized icon for the process you just launched.

Change the 2 to a 6 (vbMinimizedNoFocus), which should prevent the new process from getting the focus.

The documentation on Shell is here[^].
 
Share this answer
 
Comments
Marino_Y0301 28-Nov-22 23:52pm    
It worked fine in Windows 7, so the need to change the status was a blind spot.
I'll change it to 6.
Thanks for sharing your solution.
Dave Kreskowiak 29-Nov-22 7:56am    
It may have worked on Windows 7, but it really has nothing to do with the version of Windows. Just because code "worked before" does not mean it was written correctly back then.
Marino_Y0301 30-Nov-22 1:21am    
I still need to question the code.
As it turns out, the screen switched even with 6.
But your answer gave me a clue. When I specified 0 (vbHide) the screen didn't switch anymore!
Thanks, I was able to solve the problem.
Basically, if the app isn't interactive, don't use MsgBox at all. MsgBox is designed to freeze the current app waiting for user input, and if you are trying to run with no user interaction, then asking him to respond is probably a design flaw ...

I'd check if Application.Interactive was false, and if so, add the message to a log file instead of displaying it. Probably, I'd replace all direct calls to MsgBox with a function call - and check what to do in there so it's all in one place.
 
Share this answer
 
v2
Comments
Marino_Y0301 28-Nov-22 23:47pm    
I'm new to app design, so I hadn't thought of that. Thanks for letting me know.
But I shouldn't change the use of MsgBox this time.
I will refer to this when I create a new app. Thanks for coming up with the solution.
OriginalGriff 29-Nov-22 2:30am    
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900