Disabling security using Thisworkbook in VBA

Orton · #1 (**permalink**) 01-07-12, 19:47

Hi

I use 'workbook_open' and 'beforesave' functions in "Thisworkbook" module both to force save format at xlsm and also to check for the presence of a security device (keylok).

The probem is I've discovered by chance that "Thisworkbook" can be effectively disabled by opening and saving a workbook with the following "Thisworkbook" function:

PHP Code:


			
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.EnableEvents = False

Application.DisplayAlerts = False

ActiveWorkbook.Close True

End Sub

The issue seems to be something to do with the loop created by saving within the beforesave function. My question is whether I can prevent such a function being used to disable the beforesave etc functions within my workbooks?

Any advice would be gratefully received.

Orton · #2 (**permalink**) 01-14-12, 06:42

Ive managed to find the answer. If you don't want "thisworkbook" to be disabled by a function in another workbook which sets application.enableevents = false, insert the following in a macro module of the workbook you want to protect:

PHP Code:


			
Private Sub Auto_Open()

    Application.EnableEvents = True

End Sub

I am using this with the following "thisworkbook" function to prevent saving in anything other than the original format (in my case xlsm); I'm doing this is I understand xlsm is a more secure format than say xls - especially if workbook protection is used:

PHP Code:


			
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.EnableEvents = False

Application.DisplayAlerts = False

ChDir "C:\******\"

ActiveWorkbook.Save

Application.EnableEvents = True

Application.EnableCancelKey = xlDisabled

ActiveWorkbook.Close False

End Sub

Previously I could effectively disbable this function using the function in my original post attached to another workbook - and then opening in the same window.

Internet Services

Web Development

Digital Marketing

Consumer Tech