Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Workbook Before Save Event
Thanks for the replay Dave. I did think about doing your suggestion before I
read your post, but I decided not too. This is the code that I had to use. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) With ThisWorkbook .IsAddin = True .Save End With End Sub I find it odd that I have to put ThisWorkbook.Save in the BeforeSave Event. If I don't change the IsAddIn property the ADD IN workbook saves just fine, but when I change the property it won't save. Could this be a bug in Excel or does Excel do this for a particular reason? What is a good resource to ask Microsoft if this is a bug? Keep in mind that the ADD IN workbook is referenced by the QUOTES workbook, thus the ADD IN workbook is hidden and contains all the code to manipulate data in QUOTES. The only thing in QUOTES is a bunch of buttons which call Subs in ADD IN. -- Cheers, Ryan "Dave Peterson" wrote: I would drop the before_save event (kind of) and give the user another dedicated button that would do the save. I'd use this inside the add-in.xla ThisWorkbook module: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True MsgBox "Please use the dedicated button to save!" End Sub Then I'd have another button on the same sheet that does the save. I'd assign it to this macro: Option Explicit Sub testme01() Dim AmIAnAddin As Boolean Dim ActWindow As Window Set ActWindow = ActiveWindow With Workbooks("add-in.xla") AmIAnAddin = .IsAddin 'stop workbook_beforesave from firing Application.EnableEvents = False .IsAddin = True Application.EnableEvents = True .IsAddin = False 'keep it visible 'but mark it as saved .Saved = True End With ActWindow.Activate 'back to the window that was active End Sub (maybe add some more checks, too--like that add-in.xla is really open???) If you (as the developer) are making changes to the addin and want to save, you'll have to have a way around that Workbook_BeforeSave procedure. You could disable events (using the immediate window), save, and then enable event. Or you could check your username (application.username) to see if it's ok to save normally: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if Application.username = "Ryan Hxxx" then 'do nothing else Cancel = True MsgBox "Please use the dedicated button to save!" end if End Sub I added a couple of x's to your name (Tools|Options|General tab in xl2003 menus). Then you can be a developer and a tester/user--just by toggling your username (without changing the code). Ryan H wrote: I have a workbook named "QUOTES" that references another workbook named "ADD IN". ADD IN is saved as an add in, aka IsAddIn = True. I have a button in QUOTES that changes the IsAddIn property = False which exposes the ADD IN worksheets for editting by the user. Then when the user makes his edits and clicks the save button I want the IsAddIn property set back to True thus re-hidding the workbook to its orginal state. I do this in the BeforeSave Event. This is the problem. I make my edits, click save in ADD IN, the workbook is hidden and everything seems fine. But for some reason when I close QUOTES I get prompted "Do you want to save ADD IN?" If I click NO, my edits are not saved. So for some reason ADD IN isn't saving. Why? Sub cmbEditAddIn_Click() ' show add in workbook With ThisWorkbook .IsAddin = False .Activate End With End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Cancel = False Then ThisWorkbook.IsAddin = True End Sub -- Cheers, Ryan -- Dave Peterson . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Workbook Before Save Event
I'm not sure if it's a bug or a design choice.
I would have used: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) With Me .IsAddin = True Application.EnableEvents = False .Save Application.EnableEvents = True 'msgbox .saved End With End Sub And if the only thing that Quotes workbook does is to give the users an interface to the macros in the addin, I would dump the quotes workbook and use a dedicated toolbar or a group on the ribbon. For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm Ryan H wrote: Thanks for the replay Dave. I did think about doing your suggestion before I read your post, but I decided not too. This is the code that I had to use. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) With ThisWorkbook .IsAddin = True .Save End With End Sub I find it odd that I have to put ThisWorkbook.Save in the BeforeSave Event. If I don't change the IsAddIn property the ADD IN workbook saves just fine, but when I change the property it won't save. Could this be a bug in Excel or does Excel do this for a particular reason? What is a good resource to ask Microsoft if this is a bug? Keep in mind that the ADD IN workbook is referenced by the QUOTES workbook, thus the ADD IN workbook is hidden and contains all the code to manipulate data in QUOTES. The only thing in QUOTES is a bunch of buttons which call Subs in ADD IN. -- Cheers, Ryan "Dave Peterson" wrote: I would drop the before_save event (kind of) and give the user another dedicated button that would do the save. I'd use this inside the add-in.xla ThisWorkbook module: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True MsgBox "Please use the dedicated button to save!" End Sub Then I'd have another button on the same sheet that does the save. I'd assign it to this macro: Option Explicit Sub testme01() Dim AmIAnAddin As Boolean Dim ActWindow As Window Set ActWindow = ActiveWindow With Workbooks("add-in.xla") AmIAnAddin = .IsAddin 'stop workbook_beforesave from firing Application.EnableEvents = False .IsAddin = True Application.EnableEvents = True .IsAddin = False 'keep it visible 'but mark it as saved .Saved = True End With ActWindow.Activate 'back to the window that was active End Sub (maybe add some more checks, too--like that add-in.xla is really open???) If you (as the developer) are making changes to the addin and want to save, you'll have to have a way around that Workbook_BeforeSave procedure. You could disable events (using the immediate window), save, and then enable event. Or you could check your username (application.username) to see if it's ok to save normally: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if Application.username = "Ryan Hxxx" then 'do nothing else Cancel = True MsgBox "Please use the dedicated button to save!" end if End Sub I added a couple of x's to your name (Tools|Options|General tab in xl2003 menus). Then you can be a developer and a tester/user--just by toggling your username (without changing the code). Ryan H wrote: I have a workbook named "QUOTES" that references another workbook named "ADD IN". ADD IN is saved as an add in, aka IsAddIn = True. I have a button in QUOTES that changes the IsAddIn property = False which exposes the ADD IN worksheets for editting by the user. Then when the user makes his edits and clicks the save button I want the IsAddIn property set back to True thus re-hidding the workbook to its orginal state. I do this in the BeforeSave Event. This is the problem. I make my edits, click save in ADD IN, the workbook is hidden and everything seems fine. But for some reason when I close QUOTES I get prompted "Do you want to save ADD IN?" If I click NO, my edits are not saved. So for some reason ADD IN isn't saving. Why? Sub cmbEditAddIn_Click() ' show add in workbook With ThisWorkbook .IsAddin = False .Activate End With End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Cancel = False Then ThisWorkbook.IsAddin = True End Sub -- Cheers, Ryan -- Dave Peterson . -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Workbook Before Save Event
Yes I need to disable events. Thanks for all the links.
-- Cheers, Ryan "Dave Peterson" wrote: I'm not sure if it's a bug or a design choice. I would have used: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) With Me .IsAddin = True Application.EnableEvents = False .Save Application.EnableEvents = True 'msgbox .saved End With End Sub And if the only thing that Quotes workbook does is to give the users an interface to the macros in the addin, I would dump the quotes workbook and use a dedicated toolbar or a group on the ribbon. For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm Ryan H wrote: Thanks for the replay Dave. I did think about doing your suggestion before I read your post, but I decided not too. This is the code that I had to use. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) With ThisWorkbook .IsAddin = True .Save End With End Sub I find it odd that I have to put ThisWorkbook.Save in the BeforeSave Event. If I don't change the IsAddIn property the ADD IN workbook saves just fine, but when I change the property it won't save. Could this be a bug in Excel or does Excel do this for a particular reason? What is a good resource to ask Microsoft if this is a bug? Keep in mind that the ADD IN workbook is referenced by the QUOTES workbook, thus the ADD IN workbook is hidden and contains all the code to manipulate data in QUOTES. The only thing in QUOTES is a bunch of buttons which call Subs in ADD IN. -- Cheers, Ryan "Dave Peterson" wrote: I would drop the before_save event (kind of) and give the user another dedicated button that would do the save. I'd use this inside the add-in.xla ThisWorkbook module: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True MsgBox "Please use the dedicated button to save!" End Sub Then I'd have another button on the same sheet that does the save. I'd assign it to this macro: Option Explicit Sub testme01() Dim AmIAnAddin As Boolean Dim ActWindow As Window Set ActWindow = ActiveWindow With Workbooks("add-in.xla") AmIAnAddin = .IsAddin 'stop workbook_beforesave from firing Application.EnableEvents = False .IsAddin = True Application.EnableEvents = True .IsAddin = False 'keep it visible 'but mark it as saved .Saved = True End With ActWindow.Activate 'back to the window that was active End Sub (maybe add some more checks, too--like that add-in.xla is really open???) If you (as the developer) are making changes to the addin and want to save, you'll have to have a way around that Workbook_BeforeSave procedure. You could disable events (using the immediate window), save, and then enable event. Or you could check your username (application.username) to see if it's ok to save normally: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if Application.username = "Ryan Hxxx" then 'do nothing else Cancel = True MsgBox "Please use the dedicated button to save!" end if End Sub I added a couple of x's to your name (Tools|Options|General tab in xl2003 menus). Then you can be a developer and a tester/user--just by toggling your username (without changing the code). Ryan H wrote: I have a workbook named "QUOTES" that references another workbook named "ADD IN". ADD IN is saved as an add in, aka IsAddIn = True. I have a button in QUOTES that changes the IsAddIn property = False which exposes the ADD IN worksheets for editting by the user. Then when the user makes his edits and clicks the save button I want the IsAddIn property set back to True thus re-hidding the workbook to its orginal state. I do this in the BeforeSave Event. This is the problem. I make my edits, click save in ADD IN, the workbook is hidden and everything seems fine. But for some reason when I close QUOTES I get prompted "Do you want to save ADD IN?" If I click NO, my edits are not saved. So for some reason ADD IN isn't saving. Why? Sub cmbEditAddIn_Click() ' show add in workbook With ThisWorkbook .IsAddin = False .Activate End With End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Cancel = False Then ThisWorkbook.IsAddin = True End Sub -- Cheers, Ryan -- Dave Peterson . -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with Workbook Before Save Event | Excel Programming | |||
Save Copies of Workbook with Before Save Event | Excel Programming | |||
Exiting WorkBook Save event | Excel Programming | |||
Is there a way to Trigger a Macro on a workbook save event | Excel Programming | |||
BeforeSave sub won't save another workbook when triggered by another event sub | Excel Programming |