Protect workbook during crash

[jpen]

Hi everyone,

I have a workbook with VBA code. One of the worksheet is Proteced and the VBA code is Protected.

The VBA code is unprotecting the worksheet and afterwards protect it again.

The programm is loading data from an external source (*.txt files). Sometimes the Excel file crashes due to the fact that an user has no right to access a direcory or file.
In this case the when re-opening Excel the worksheet is unprotected. After running VBA code the worksheet is portected again.


How can I change the VBA code so the worksheet is protect after a crash?

 

[Joe4]

The VBA code is unprotecting the worksheet and afterwards protect it again.

Maybe this approach.

Also add the code that you have that protects it again to the BeforeSave event in VBA. Then, every time anyone tries to save the file, it will re-protect that code.
So, assuming that they haven't disabled VBA, it will be impossible to save the file with it unprotected.

 

[Fluff]

Alternatively, put something in a Workbook_Open event that protects the sheet.
That way as long as macros are enabled, the sheet will be protected.

 

[Joe4]

Alternatively, put something in a Workbook_Open event that protects the sheet.
That way as long as macros are enabled, the sheet will be protected.

I considered that too, however I like the BeforeSave is the better option. Here is why:

Both of these methods are subject to "Macros/VBA" being enabled. If it is first unprotected by VBA code running, then the only which in which it could/should become unprotected is if they have already enabled VBA code. So since we know that they have already enabled VBA code, then we know that the BeforeSave code should fire/work, and they will not be able to Save the file in an unprotected state.

However, if the file is unprotected, and they Open it up, if they do not elect to Enable VBA/Macros, they will be able to open the file in an unprotected state.
So, if you have a user whose goal was to try to open it up in an unprotected state, it seems it would be much easier to do if the code was in the Open event instead of the BeforeSave event. So the BeforeSave event would seem to be a more effective approach, in my opinion.

Or, if you REALLY wanted to have it as protected as possible, you could add the code to BOTH events (Open and BeforeSave)...

 

[Fluff]

Totally agree with you Joe, the only reason I suggested an Open event is because of this

How can I change the VBA code so the worksheet is protect after a crash?

If Xl crashes I doubt that the BeforeSave will fire.

 

[Joe4]

If Xl crashes I doubt that the BeforeSave will fire.

Very true, but if it never Saves, then it should never save the file in the state where it is left unprotected!
So, when they open up the file the next time, it will be in the previous saved state, that was before when they attempted to run the code that crashed it.

 

[Fluff]

Excellent point Joe
Never thought of that