[ESC] and [CTRL] + [BREAK] don't seem to work

[squidgeny]

I'm having a rather serious problem.

My macro uses a lot of SendKeys commands to operate another program. It's also full of make-shift delays between the SendKeys commands, using a function that runs a pointless loop.

Sometimes I need to suddenly Exit the macro; to stop further SendKeys commands from running. But despite what I've read, using either Esc or Ctrl+Break just doesn't seem to work!

The only way I seem to be able to exit it is with the Task manager.

Any ideas as to why this is so?

 

[Norie]

If you are working with another application the CTRL+BREAK might be getting 'sent' to that rather than Excel.

That's probably what's happening, though I have seen VBA not respond to to things like that before.

 

[squidgeny]

Hmm I think you're right. That's a bit of a pain.

Is there any way I could code a dedicated kill-switch into my macro, then? If I could do that, that would be perfect.

 

[squidgeny]

I don't like to bump threads but they get buried easily and I'm sure there's a solution to this one! Thanks.

 

[Norie]

Perhaps you could start by getting rid of, or altering the 'pointless' loop.

Other than that what would be the trigger for your killswitch?

I suppose if you ran the code from a form then you could add a button and have a real switch.

Using DoEvents would probably help with monitoring for it being clicked while the rest of the code is still running.

 

[squidgeny]

The pointless loop is there to cause a delay between SendKeys commands... I've had a good look around the internet for code that would create delays and the best I can come up with is this function:

Function Delay(seconds As Integer)

    Dim StartTime As Date
    StartTime = Now
    
    Do While DateDiff("s", StartTime, Now) < seconds
    Loop
    
End Function

Ideally I'd like the killswitch trigger to just be a key on the keyboard (that works whether or not Excel is in focus). But it's possible there's no way to do that in VBA.

The macro is literally just a list of SendKeys commands... it starts with an Alt+Tab to switch to another program and the rest interact with that program (navigating menus, selecting options etc). Some commands also send strings from the spreadsheet to the program (i.e. SendKeys mystring, True). It loops a bit to do the same thing repeatedly, and then ends.

I think what I may have to do instead, is use AutoIt, and program in a few Alt+Tabs and Ctrl+C/V to copy data over from the spreadsheet into the program instead. It's a bit messier but I know that AutoIt supports a killswitch.