In order to head off a storm of "comment it out" replies, here is my situation:
I have a process is normally run 1 iteration by 1 iteration. A user manually hits a button that calls a macro which, upon completion, pops up a message box that reports the total time length the macro ran for. It's pretty handy for diagnosing issues. This code is locked down and I cannot modify it.
I am trying to do this at scale. Because the code in the main spreadsheet and workbook are locked, I have a separate workbook open in the same instance of excel with a macro that operates the locked down workbook. Rather than 1 by 1, I've got a set of 300 I'm trying to run through. Right now I have to babysit the thing and hit space to get past the MsgBox. Does anyone know of any tricks to prevent me having to monitor the thing? Either disabling the pop-ups or some way to make them non-modal. Maybe a trick to make the mouse click?
You're right in knowing that the best way to fix the issue is to correct the code. In which case you would probably make the pop-ups toggle-able.
However, I wrote this for you which could be used as a potential work around. It utilizes VBScript to "sort-of" simulate multithreading so that you can send a key to the modal Msgbox. Assuming you can do what you want to do via code, simply call SendDelayedKeys before the action that will cause a Msgbox. You may have to tinker with the Delay based upon your circumstances as 100 milliseconds may not be enough. To change the Delay, just call like this: SendDelayedKeys 500 for 500 milliseconds.
Sub SendDelayedKeys(Optional Delay As Long = 100, Optional keys As String = """ """)
Dim oFSO As Object
Dim oFile As Object
Dim sFile As String
sFile = "C:\SendKeys.vbs" 'Make this a valid path to which you can write.
'Check for the .vbs file.
If Not Len(Dir$(sFile)) Then
'Create the vbs file.
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.CreateTextFile(sFile)
oFile.WriteLine "Set WshShell = WScript.CreateObject(""WScript.Shell"")"
oFile.WriteLine "WScript.Sleep CLng(WScript.Arguments(0))"
oFile.WriteLine "WshShell.SendKeys WScript.Arguments(1)"
oFile.Close
End If
Shell "wscript C:\SendKeys.vbs " & Delay & " " & keys
End Sub
Sub ProofOfConcept()
'Using default parameters which sends a space after 100 milliseconds
SendDelayedKeys
MsgBox "I disappear on my own!"
End Sub
A word of warning: Any solution that utilizes SendKeys is a fragile solution and should be avoided when possible. However, when your options are limited and you need to avoid a manual process, sometimes it's your only option.
Since SiddhartRout rightly pointed out that this could be solved using API calls: here's a link with C# code that would close your msgbox every second.
The problem here really isn't strictly a problem more code can (or indeed should) solve.
There are a great many things to consider and any solution will be more complex AND less reliable than the problem it is initially trying to solve. But lets look at your options...
SendKeys is not reliable for that kind of use, what happens if the dialogue says "would you like me to save this workbook?" just after making a change that was meant to be temporary or "would you like to play global thermonuclear war?" Plus with a batch process like that you want to get on with something else while you wait, even if it's only to come here to downvote trolls. If nothing else you may not be in control of this code so what kind of mess will it cause when the maintainers realise msgbox is bad UX and kill it?
FindWindow API calls would let you check the content in the window to make sure it says what you're expecting but then you're potentially asking some bit of quick & dirty vbscript to go into a race condition until the right window comes up. Can you guarantee that the threads won't lock up?. What about platform issues - what happens if someone wants to run your code on their shiny new surface? What happens when your 64 bit modal Excel dialogue window can't be seen by the 32-bit api calls you were making? What about a new version of office that doesn't present modal dialogues in the same way? None of those problems are insurmountable but each adds complexity (aka opportunity for failure.)
The best solution is fix the actual problem you have identified from the outset which is that the original code throws up an unnecessary modal dialogue. Someone needs to fix that - it doesn't have to be you but if you estimate how much time that modal dialogue wastes in lost productivity that should get you a solid business case for getting it sorted.
