Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Unanswered: Beforesave with Enableevents disabled

    Hi, hoping someone can help me.

    For security reasons I'd like to force users to save as xlsm format only. But I also want to stop someone with malicious intent to save in a different format such as xls. I can add a beforesave function but these are easiy disabled by opening another workbook in the same window and running a function which sets enableevents = false. Once set all functions in the "thisworkbook" module are disabled so the beforesave funcion does not work and save can be in any format. Does anyone know a way around this?:

    - is there a way to enable a before save function to work even with events disabled? (eg an equivalent to the auto_open option which takes over from the beforesave function when events are disabled)

    - is there some code I can add to the auto_open function to force the beforesave function to work even when events are disabled?

    - is there a way to ensure events are automatically enabled when the focus shifts back to the workbook being proeted?

    I've researched various options but they all seem to fall down in the scenario where events are disabled as the yare private subs in the "thiswrokbook" module.

    Regards
    Wayne Orton

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Wayne,
    I can add a beforesave function but these are easiy disabled by opening another workbook in the same window and running a function which sets enableevents = false
    Even easier, just open up the VBA IDE and type Application.EnableEvents=False into the immediate window - there's no need to open a new workbook.
    - is there a way to enable a before save function to work even with events disabled? (eg an equivalent to the auto_open option which takes over from the beforesave function when events are disabled)
    No, there isn't an auto_beforesave you can use.
    - is there some code I can add to the auto_open function to force the beforesave function to work even when events are disabled?
    No. Auto_Open() runs once when the workbook is opened. The user could just disable events after Auto_Open() has run.
    - is there a way to ensure events are automatically enabled when the focus shifts back to the workbook being proeted?
    No. You would do this using a combination of WorkbookActivate and SheetActivate event handlers. Unfortunately these are of no use in this situation because they will not run if Application.EnableEvents has been set to False.


    Unfortunately, even if you find a resolution to this (for example, putting in measures to prevent the user having access to the VBA IDE), you would then find further problems. For example:

    The user could disable macros and then the prevention measures you've put in wouldn't take effect when they open the project.

    If the user would be clever enough to circumvent your Workbook_BeforeSave event handler by disabling application events, then that user would also be clever enough hack the VBA password protection to your project and change your project's code as required (eg, remove your Workbook_BeforeSave event handler entirely).

    The bottom line is that Excel isn't a secure environment and anyone who is determined enough will be able to remove any protection or precautions you put into the project.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •