Problems with Workbook Before Save Event

[Ryan H]

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

 

[Peter T]

I think you are only giving half the details, I don't follow your scenario
as what you have given doesn't make sense, eg

Sub cmbEditAddIn_Click()

' show add in workbook
With ThisWorkbook
.IsAddin = False
.Activate
End With

Surely by definition ThisWorkbook is already active otherwise user cannot
see the button, or is there more to it.

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?

As you explain above surely the answer is obvious, because you clicked No to
the prompt asking if you want to save ADD IN.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Cancel = False Then ThisWorkbook.IsAddin = True
End Sub

Cancel is always False unless you change it to True, thereby aborting the
Save, why the If or is there more to it.

Wild guess, the file is getting saved but to different locations depending
on it's Addin property.

Try and explain your setup

Regards,
Peter T

 

[JLGWhiz]

Try adding a save line in:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

If Cancel = False Then ThisWorkbook.IsAddin = True
ThisWorkbook.Save
End Sub

 

[Dave Peterson]

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]

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.

 

[Ryan H]

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

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

 

[Dave Peterson]

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

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.

 

[Ryan H]

Yes I need to disable events. Thanks for all the links.
--
Cheers,
Ryan

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