Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin not allowing thisworkbook.save in before save event
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin not allowing thisworkbook.save in before save event
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 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 at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin not allowing thisworkbook.save in before save event
On Jan 27, 6:04*pm, GS wrote:
Stexwas 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 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 - 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin not allowing thisworkbook.save 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). -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save Copies of Workbook with Before Save Event | Excel Programming | |||
Allowing the user to specify save location | Excel Programming | |||
Allowing User to save file | Excel Programming | |||
protected code allowing save | Excel Programming |