Addin not allowing thisworkbook.save in before save event

  • Thread starter Thread starter Stex
  • Start date Start date
S

Stex

Hi all,

I have an addin that I cannot remove or disable that is interfering
with the before save event. Everything runs but the thisworkbook.save
line does nothing. I want to take control of the event to put a nice
splash screen warning that macros should be enabled before the save
and hide them afterwards. The workbook open event hides them provided
macros are enabled. The macro is as follows;

Application.ScreenUpdating = False
ActiveSheet.Shapes("shpmacro").Visible = True
Application.EnableEvents = False
ThisWorkbook.Save <==== doesnt actually save the
workbook
Application.EnableEvents = True
ActiveSheet.Shapes("shpmacro").Visible = False
Application.ScreenUpdating = True
ThisWorkbook.Saved = True

I dont want to use the before close event as myself I hate it when a
workbook saves when I dont expect it to. ie I like to muck around
with a sheet and if I dont like it close without saving.

Is there another way to save the workbook or provide a nice message
to politely ask the user to allow the macros


Stex
 
Stex was thinking very hard :
Hi all,

I have an addin that I cannot remove or disable that is interfering
with the before save event. Everything runs but the thisworkbook.save
line does nothing. I want to take control of the event to put a nice
splash screen warning that macros should be enabled before the save
and hide them afterwards. The workbook open event hides them provided
macros are enabled. The macro is as follows;

Application.ScreenUpdating = False
ActiveSheet.Shapes("shpmacro").Visible = True
Application.EnableEvents = False
ThisWorkbook.Save <==== doesnt actually save the
workbook
Application.EnableEvents = True
ActiveSheet.Shapes("shpmacro").Visible = False
Application.ScreenUpdating = True
ThisWorkbook.Saved = True

I dont want to use the before close event as myself I hate it when a
workbook saves when I dont expect it to. ie I like to muck around
with a sheet and if I dont like it close without saving.

Is there another way to save the workbook or provide a nice message
to politely ask the user to allow the macros


Stex
Click to expand...

Normally, there would be no reason for an 'Addin' to save itself so it
makes me leary that you want to save it via the UI since XLAs are
hidden to begin with, and the only way you can access its sheets via
the UI is if you set its 'IsAddin' property to FALSE.

Excel has a built-in macro notification that prompts users to
Enable/Disable macros when the file is opened. Since you're talking
about an 'Addin' (xla) then this wouldn't apply if the addin is
installed/managed via the 'Addins Manager'.

You could be better helped if you were more forthcoming about what you
are doing EXACTLY.<g>
 
Stexwas thinking very hard :











Normally, there would be no reason for an 'Addin' to save itself so it
makes me leary that you want to save it via the UI since XLAs are
hidden to begin with, and the only way you can access its sheets via
the UI is if you set its 'IsAddin' property to FALSE.

Excel has a built-in macro notification that prompts users to
Enable/Disable macros when the file is opened. Since you're talking
about an 'Addin' (xla) then this wouldn't apply if the addin is
installed/managed via the 'Addins Manager'.

You could be better helped if you were more forthcoming about what you
are doing EXACTLY.<g>

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

My model is merely a spreadsheet with some macro functionality in
particular a UDF that needs macros enabled. I'm planning to
distribute it to others outside the company I'm working for hence I'm
trying to find a nice way of telling users you need to turn on macros
to use this workbook rather than the standard MS "the world will cave
in and you'll get boils on your face if you enable macros".

My approach was to take control of the save event and

1) redirect to an instruction sheet,
2) save the workbook
3) put sheet back to where the user was

In the workbook open event
1) hide the information sheet

That way users would not notice anything if macros were enabled but if
they do not enable macros that would end up on the information sheet
saying why macros are needed and how to do it if they so choose.

My problem is in my corporate environment we have an addin (not mine)
called "Objective". I dont know too much about it but it basically
intercepts any open or close event within excel/word to redirect to
offer people a new dialog to save to a corporate area. I cannot
disable it or see it under my list of addins, but its there alright.
It lets me code to before save event but will not run a
thisworkbook.save from within there. Basically stopping my approach
from working. As I dont know everyones environment I'm reluctant to
use this approach now in case they have a similar addin.

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

Cheers
Steve
 
"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"
Click to expand...

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).
 
Back
Top