The wrong workbook gets saved!

[Rob]

I have a menu option in my addin which lets the user see and change a
parameters sheet, with paths to other workbooks etc.

Sub ShowParameters(FakeArg as boolean)
thisworkbook.isaddin=false
end sub

To ensure that it doesn't get saved with the parameters showing, I've put
the following code in the ThisWorkbook module of the addin:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _

Cancel As Boolean)
Msgbox "The BeforeSave event of the addin was triggered"
ThisWorkbook.IsAddin=true
'After this runs, the ActiveWorkbook gets saved instead of this one.
End Sub

If there is any other workbook open, then it gets saved instead of the
addin. Is this a bug in Excel or am I doing something wrong???

Thanks
Rob

 

[Robin Hammond]

Rob,

Can't say I've tried this so it is guesswork on my part but how about trying

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
ThisWorkbook.IsAddin=true
ThisWorkbook.Save
Cancel = true
End Sub

If that doesn't work, I think most of us would probably have the addin
display a parameters form rather than using the IsAddIn property. Even
better, I would probably then save the parameters in the registry using
SaveSetting and GetSetting rather than save the add-in itself every time
there is a change.

Robin Hammond
www.enhanceddatasystems.com