VBA code to imitate VBE's "Reset" command under the "Run" menu

[Donb209]

When I am in my developer mode I do what code additions or changes i need to do then go back to Excel worksheet to execute a command button to test it. Now nothing ever fails, and when you get through laughing, read on. If, or rather when an error occurs (who knew spelling could be such a problem) I go back to VBE and fix it. When I go back to worksheet to run it, it won't. OOOOPs - forgot to "reset" the run command in VBE. So back I go just to do a reset.
Now the question - is there a way to do that reset without going back to VBE. It seems everything gets frozen until the reset is executed which kind of eliminates running another macro from the worksheet to do the reset, but how about a keyboard combo?

 

[RoryA]

The nearest thing in VBA would be the End statement. It sounds like perhaps you just turned screenupdating off and when you stopped the code you didn't re-enable it, so perhaps a simple routine to do that would suffice.

 

[Donb209]

The nearest thing in VBA would be the End statement. It sounds like perhaps you just turned screenupdating off and when you stopped the code you didn't re-enable it, so perhaps a simple routine to do that would suffice.

Thanks for the reply1
While I do routinely use the below entry code in my macros, errors end the the macro before I reach the closing where I set the lines back. That is not the problem.

Entry code -
With Application
.ScreenUpdating = False
.DisplayStatusBar = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

closing code-
With Application
.ScreenUpdating = True
.DisplayStatusBar = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With

When an error is encountered - such as a goto with out a reference for the goto, I pull up the code and fix it, but if I forget to go up to the "Run" menu item in VBE and click the "Reset" when I go back to the worksheet, I am frozen out of everything; I can't select a different sheet, or execute anything else. I must pull up the Editor again and click the "Reset" to get going again.
I just want a simple means to execute the "Reset" command from within the worksheet without going back to the editor.

 

[RoryA]

That was precisely my point. If you hit an error without re-enabling screenupdating, Excel will appear to be frozen (even though it isn't really). I'd suggest you start by trying a macro that simply re-enables screenupdating.

 

[Donb209]

That was precisely my point. If you hit an error without re-enabling screenupdating, Excel will appear to be frozen (even though it isn't really). I'd suggest you start by trying a macro that simply re-enables screenupdating.

Please forgive my ignorance, but if you don't know where the error is goin to occur, how do you know when to allow screen updating? I am guessing that the "Reset" button enables screen updating. Most, if not all my macros use the previously mentioned entry code right after entering the macro and use the close code just before the end sub. I am starting to get the feeling that I should not use the entry code and close code or at the very least drop the screenupdating from those codes. I did try running my exit code at the very beginning of the macro and commented out the entry code . It still did allow moving around within the sheets after an error is hit and fixed, but no commandbuttons worked etc.
I guess I am just totally confused. Finally, i removed all screenupdating references within the macro and the same situation exists. I am not so concerned about it, as when an error occurs, I expect all to stop. I just really want a way to reset the execution ability after I fix the error. Specifically from the worksheet area of Excel rather than from the Editor for VBA.

 

[Donb209]

OK, I found what it was I wanted. Another board had a similar question and while the answer wasn't exactly what worked for me , i was pointed to the right area to look and after some expermintating found what works.

If an error interrupts the running program and you go into the editor and fix it then jump back to Excel, nothing works. Just hit "Alt F4" and it closed the editor and asks if you want to exit. Just say no and continue to work. Everything works and the editor is still there.

Thanks for spending the time trying to help this poor soul who couldn't express himself well enough! Being old and feeble minded isn't for the weak .

 

[Donb209]

OK, I found what it was I wanted. Another board had a similar question and while the answer wasn't exactly what worked for me , i was pointed to the right area to look and after some expermintating found what works.

If an error interrupts the running program and you go into the editor and fix it then jump back to Excel, nothing works. Just hit "Alt F4" and it closed the editor and asks if you want to exit. Just say no and continue to work. Everything works and the editor is still there.

Thanks for spending the time trying to help this poor soul who couldn't express himself well enough! Being old and feeble minded isn't for the weak .

OOOOps - that should have been "Alt F5" above. Bad fingers. Tsk, Tsk!

 

[RoryA]

Apparently I wasn't good at explaining myself either. What I meant was that you could create a macro that does nothing but:

Sub ResetScreen()
application.screenupdating = true
end sub

assign a keyboard shortcut to it, and then run that as a reset routine when needed.