Re: Addin not allowing in before save event

"It's not the end of the world and I can cope with the "boils on ya
face" message just wondering if there's any other options out there"

Well, I don't think redistributing a workbook with macros is what you want to do since the user must read your instruction, close the workbook, change macro security, and reopen the workbook to enable them. If they set macro security to 'Medium' they see a warning. If they change it to 'Low' they see nothing.

Your corporate addin "Objective" is probably a COMAddin (in-process DLL loaded by Excel at startup) and so will not show up in the VBA editor.

If, as you say, you only want to distribute your 'addin' outside your company then I wouldn't worry about the Workbook_BeforeSave event because that's not where you should put your 'ThisWorkbook.Save' statement. Use a procedure in a standard module that replaces the Workbook_Open and Workbook_BeforeSave events...

Sub Auto_Open()
'//put code to run when workbook opens
End Sub

Sub Auto_Close()
'//put code to run when workbook closes
'//optionally, include before close code
End Sub

As I said, there should be no reason to save your workbook if it is indeed an addin because there should NEVER be changes made to an addin during runtime that don't get cleaned up by the addin before shutdown. If your addin provides functionality to users for use in THEIR workbooks then it would be THEIR workbooks that need to be saved, NEVER your addin because that means the next version release of your addin will replace whatever was saved. Addins provide functionality to Excel for use by users in THEIR projects -OR- in a template workbook that belongs to your addin which users can SaveAs THEIR project.

Any changes your addin makes to itself during runtime can be undone before shutdown -OR- escaped by setting the addin's 'Saved' property to 'True' before shutdown.

Also, if you need to escape inherent Excel behaviors/notifications you can wrap your subject code in event handlers as follows...

'Disable settings your code needs to escape
With Application
.EnableEvents = False: .DisplayAlerts = False
End With

'//do stuff you don't want trapped by the above settings

'Restore settings to normal
With Application
.EnableEvents = True: .DisplayAlerts = True
End With

Finally, your addin could run in an automated instance of Excel, which has no macro security to worry about AND it also does not load other addins of any kind. This is my preferred approach because my projects are 'task-specific' and so they extensively modify and lock down Excel's UI so they 'appear' as separate apps (so much so that most users don't even know they're using Excel if using a version prior to XL2007).


Free usenet access at
ClassicVB Users Regroup! comp.lang.basic.visual.misc