PDA

View Full Version : restore workbook after Application.Visible = False



fscarpa58
04-06-2019, 06:14 AM
hi,

i have a macro that i want to run but keep the workbook hidden.
so i added

Application.Visible = False
.

this works to hide the workbook however when some unhandled error occurs and the button "finish" is pressed (I really do not know if it ir actually "finish" or "end" since in Italian it says "Fine") my original workbook is not restored.
Yes, I can put

Application.Visible = True
.
in the immediate windows, but I am looking for something to programmatically intercepts that event and restore the original workbook.
any suggestions?

thanks

Paul_Hossler
04-06-2019, 06:37 AM
Try putting 'On Error … ' in the top. If an error occurs, the macro will jump to the MakeVisible: line and continue




Sub Something


….

On Error GoTo MakeVisible

...code …



MakeVisible:


Application.Visible = True


End Sub

fscarpa58
04-06-2019, 09:18 AM
Sub Something
...
On Error GoTo MakeVisible

...code …



Thank you Paul

Actually, however, I do not know in which subroutine errors can happen. So I cannot use the On Error statement.
For normal behavior (w/o errors) I restore the workbook by using the following



Private Sub UserForm_Terminate()
Application.Visible = True
End Sub




It works fine when I normally exit the application but it fails to intercept unhandled errors or,
better, to intercept what happens after the the button "End" (on the error form) is pressed.

federico

Paul_Hossler
04-06-2019, 11:20 AM
Do you have any other On Error statements?

fscarpa58
04-06-2019, 12:05 PM
Do you have any other On Error statements?

Yes, about five or six

Paul_Hossler
04-06-2019, 01:08 PM
Only one can be in effect

You might have to make single, smarter error handler and use the Err.Number

Just an example




Select Case Err.Number

Case 1004
….. something

Case 9876

Case Else
Application.Visible = True

End Select

MagPower
04-06-2019, 01:09 PM
Federico,

You probably should attach a sample application (with non-sensitive data) so that we can see what's going on.

Also, just curious, why does the application have to be hidden?

fscarpa58
04-06-2019, 03:02 PM
Tomorrow I'll try to explain Better. Anyway I am confused on where to Put the on error statement since there are fifty more subroutines. I dont Know where error Will happpen because I am developing the software. I want to hide the sheets since they Bore me.

fscarpa58
04-08-2019, 03:10 AM
Only one can be in effect



Is there a place in which I can put the on error statement in such a way it will intercept ALL the errors in a project, with various forms and modules?
thank you
federico

Paul_Hossler
04-08-2019, 06:28 AM
On Error can be tricky

Chip has a good writeup

http://www.cpearson.com/excel/errorhandling.htm



and in particular depending on the overall structure) ....




Error Handling With Multiple Procedures

Every procedure need not have a error code. When an error occurs, VBA uses the last On Error statement to direct code execution. If the code causing the error is in a procedure with an On Error statement, error handling is as described in the above section. However, if the procedure in which the error occurs does not have an error handler, VBA looks backwards through the procedure calls which lead to the erroneous code. For example if procedure A calls B and B calls C, and A is the only procedure with an error handler, if an error occurs in procedure C, code execution is immediately transferred to the error handler in procedure A, skipping the remaining code in B.