How to interrupt a VBA macro stuck in an InputBox loop?

[JenniferMurphy]

I can't post the exact code because its stuck in an InputBox loop. This is close to what it is. I mistakenly put the IsNumeric right after the InputBox, which catches both Cancel so there is no way to exit. How can I interrupt it? Thanks

Do
  vReply = InputBox(Time & " " & LastAction & vbCrLf & vbCrLf & Prompt, MyName, , 11000, 7000)
  'Cancel returns "", but so does "", so confirm both
  If Not IsNumric(vReply) Then GoTo BadInput

  . . .
 
BadInput:
  MsgBox "Invalid data", vbOKOnly, MyName
Continue:
Loop

 

[johnnyL]

Is the time wasted stuck in a loop waiting for a response that works better than just reevaluating the code after the restart?

 

[JenniferMurphy]

Well you can lose the last edits or stay stuck in an infinate loop where you can't do anything.

Apparently so. Is this yet another failure of the Surface Book or of Excel VBA or of Win 10? It seems to me that on my old ThinkPad running XP I was able to interrupt macros in loops like this.

 

[JenniferMurphy]

Is the time wasted stuck in a loop waiting for a response that works better than just reevaluating the code after the restart?

I wasn't just sitting here picking my nose. I do have other things to do. And I was hoping to find a solution in case I do it again, which I probably will.

 

[johnnyL]

I wasn't just sitting here picking my nose. I do have other things to do. And I was hoping to find a solution in case I do it again, which I probably will.

I wasn't suggesting that you were picking your nose. Based on my personal experience, I have to tell you, that is not going to help the situation.

If it were me, I would bite the bullet and just get it back to where you can change your mistake in the code that you mentioned. Then in your leisure time (Not picking your nose) try to google how to get the 'Break' to work so that you have that info for future reference.

 

[Z51]

My laptop doesn't have a pause or break key either, but holding down the ESC key for a few seconds (rather than tapping it) brought up the End/Debug/etc. prompt which allowed me to stop the macro.

Another option, if that doesn't work, might be to bring up the on-screen keyboard - hopefully yours has a pause or break key there. (Mine has pause, but no break.)

 

[Z51]

A good alternative I just tried is downloading Microsoft PowerToys from the Microsoft Store (it's free).

Once installed, in PowerToys go to Keyboard Manager.
Click 'Remap a key'
Click the + button to add a new mapping
Beneath 'Physical key' click Type and then tap a key you want to map. For me, I picked the backwards apostrophe key in the top left as I never use it.
Beneath 'Mapped to', click the drop-down and first select "Ctrl (Left)", then select "Break" from the second drop-down that appears.
Click Ok
Click Continue anyway

Go back to excel and tap the key you mapped and it should act just like CTRL+Break. You can then go back into PowerToys and delete the custom mapping. (Or keep it!)

Hope that helps.

 

[JenniferMurphy]

My laptop doesn't have a pause or break key either, but holding down the ESC key for a few seconds (rather than tapping it) brought up the End/Debug/etc. prompt which allowed me to stop the macro.

Did you do this with the sample code I posted here? I just tried it. When I press and hold Esc, it immediately puts up my "Invalid input" MsgBox. The code gets a null string from InputBox in response to Esc. Holding it longer does nothing.

Another option, if that doesn't work, might be to bring up the on-screen keyboard - hopefully yours has a pause or break key there. (Mine has pause, but no break.)

That's an interesting option. I never knew about that. It can be brought up using Ctrl+Win+o. Here's what mine looks like:

I do have a Pause button. Clicking it makes a soft clicking sounds, but no action on the macro code. I also tried Ctrl+Pause, Win+Pause, etc.

But thanks for showing me that keyboard.

 

[Z51]

See my latest post, re: PowerToys

 

[Alex Blakenburg]

My laptop doesn't have a pause or break key either, but holding down the ESC key for a few seconds (rather than tapping it) brought up the End/Debug/etc. prompt which allowed me to stop the macro.

Holding down the escape key worked fine on my laptop when I tried to replicate the scenario.

 

[JenniferMurphy]

A good alternative I just tried is downloading Microsoft PowerToys from the Microsoft Store (it's free).

Once installed, in PowerToys go to Keyboard Manager.
Click 'Remap a key'
Click the + button to add a new mapping
Beneath 'Physical key' click Type and then tap a key you want to map. For me, I picked the backwards apostrophe key in the top left as I never use it.
Beneath 'Mapped to', click the drop-down and first select "Ctrl (Left)", then select "Break" from the second drop-down that appears.
Click Ok
Click Continue anyway

Go back to excel and tap the key you mapped and it should act just like CTRL+Break. You can then go back into PowerToys and delete the custom mapping. (Or keep it!)

Hope that helps.

I found PowerToys here:

Install PowerToys

Install PowerToys, a set of utilities for customizing Windows, using an executable file or package manager (WinGet, Chocolatey, Scoop).

learn.microsoft.com

I have Win 10 (19044) x64 so it should work.

It says it installs several runtime environments. I hope none of that will affect anything I am doing with Edge. I don't use .NET or C++.