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

Donb209

New Member
Joined
Apr 30, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,354
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Apr 30, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,354
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Apr 30, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Apr 30, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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 .
 
Solution

Donb209

New Member
Joined
Apr 30, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,354
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Apparently I wasn't good at explaining myself either. :) What I meant was that you could create a macro that does nothing but:

VBA Code:
Sub ResetScreen()
application.screenupdating = true
end sub

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

Forum statistics

Threads
1,141,485
Messages
5,706,654
Members
421,459
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top