Best way to report VBA failures in an .XLSM

[Plasmech]

I want the users of an .XLSM that I manage to know immediately if VBA has stopped working.

My thought is to have a cell with text content "VBA FAULT" that would be suppressed/hidden by a line of VBA code.

A) Is this possible
B) How is it done
C) Any better ideas?

Thanks for any replies.

 

[Joe4]

The tricky part is that in order to log/report something like that (automation), you would probably need VBA enabled!
Obivously, if VBA is not enabled, you cannot have automation.

What I have seen some people do is to make all sheets/data (except for a cover sheet) VERY HIDDEN, and the other sheets are only exposed if VBA has been enabled.
The way they do this is to have "Workbook_Open" event procedure code (which runs automatically upon opening) that will unhide all the sheets.
If VBA code is not enabled, this macro does not run, and the sheets are not unhidden (not VERY HIDDEN sheets can not be seen/unhidden using typical manual unhide processes).
They then have "Workbook_Close" event procedure code that rehides the sheet and saves the file.

So, if they do not enable VBA code/macros, they really cannot do much of anything with the file.

 

[Plasmech]

The tricky part is that in order to log/report something like that (automation), you would probably need VBA enabled!
Obivously, if VBA is not enabled, you cannot have automation.

What I have seen some people do is to make all sheets/data (except for a cover sheet) VERY HIDDEN, and the other sheets are only exposed if VBA has been enabled.
The way they do this is to have "Workbook_Open" event procedure code (which runs automatically upon opening) that will unhide all the sheets.
If VBA code is not enabled, this macro does not run, and the sheets are not unhidden (not VERY HIDDEN sheets can not be seen/unhidden using typical manual unhide processes).
They then have "Workbook_Close" event procedure code that rehides the sheet and saves the file.

So, if they do not enable VBA code/macros, they really cannot do much of anything with the file.

Hi Joe,

First, my apologies for the delayed response.

Thank you for the reply.

Correct me if I'm wrong, but if VBA is running, it will have the ability to hide the "FAULT" cell.

If it's not running, it won't, and thus the FAULT cell will be visible.

Am I over-simplifying this?

 

[Joe4]

Hi Joe,

First, my apologies for the delayed response.

Thank you for the reply.

Correct me if I'm wrong, but if VBA is running, it will have the ability to hide the "FAULT" cell.

If it's not running, it won't, and thus the FAULT cell will be visible.

Am I over-simplifying this?

Yes, you can have VBA hide certains rows or columns, dependent upon cell values.

There is a Workbook_Open event, which runs automatically when a file is first opened.
I imagine that is probably where you would want VBA like this to go.
Just bear in mind that if the column/row is already hidden, and VBA is not enabled, the column/row cannot be automatically unhidden.

 

[Plasmech]

Yes, you can have VBA hide certains rows or columns, dependent upon cell values.

There is a Workbook_Open event, which runs automatically when a file is first opened.
I imagine that is probably where you would want VBA like this to go.
Just bear in mind that if the column/row is already hidden, and VBA is not enabled, the column/row cannot be automatically unhidden.

Looks like I could dedicate Row 1 as my FAULT row, and add:

Rows(1).Hidden = True

to the Workbook_Open code.

 

[Joe4]

Looks like I could dedicate Row 1 as my FAULT row, and add:

Rows(1).Hidden = True

to the Workbook_Open code.

And you may want to unhide it on the Workbook_Close event, to ensure it is unhidden from the start the next time it is opened.
I usually add a "ActiveWorkbook.Save" command after that too, to ensure it is saved that way.

 

[Plasmech]

And you may want to unhide it on the Workbook_Close event, to ensure it is unhidden from the start the next time it is opened.
I usually add a "ActiveWorkbook.Save" command after that too, to ensure it is saved that way.

Understood. I'll give this a go.

Thanks.

 

[Joe4]

You are welcome.