addin running in different Excel instances

[Doug Glancy]

I have an addin that saves itself in either the BeforeClose of
AddinUninstall events. If for some reason I open multiple Excel instances,
when I close the one of the later instances the line ThisWorkbook.Save saves
the addin to the active Excel directory, e.g., My Docs. No error is
generated, it just saves a copy to the wrong place.

It's not a big problem if the addins in the multiple instances are not
saved. So instead of ThisWorkbook.Save I wrote code that uses
ThisWorkbook.Saveas and surrounded it with On Error statements and turned of
DisplayAlerts:

If Not ThisWorkbook.Saved Then
'in case it's read-only, which would be true if this were the 2nd (or
more) instance of Excel
On Error Resume Next
Application.DisplayAlerts = False
'I did SaveAs, because if it's read only Save will save to the active
Excel Directory
ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName
Application.DisplayAlerts = True
On Error GoTo 0
End If
Exit Sub
ErrorHandler:
Application.ScreenUpdating = True
Application.DisplayAlerts = True

This solution falls under the category of "crude but effective, I hope."
Does anybody have a better one?

XL03 Win Vista and XP

Thanks,

Doug

 

[Bob Phillips]

I have to ask, why are you saving the addin? It is against one of the major
principles of an addin IMO.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Doug Glancy]

Bob,

There are a couple of addin settings that are stored on a sheet.

Thanks,

Doug

 

[Bob Phillips]

Wouldn't it be better to save these in the registry or in the workbook that
they are applicable to?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Or maybe in another file?

Bob,

There are a couple of addin settings that are stored on a sheet.

Thanks,

Doug

 

[RB Smissaert]

Just use a simple .ini file.

RBS

Bob,

There are a couple of addin settings that are stored on a sheet.

Thanks,

Doug

 

[Doug Glancy]

Thanks to all of you for your thoughts.

It's actually a pretty trivial little with one button that can be added to
the Standard or Formatting toolbar. Normally, I'd add it to one of my own
utility menus, but since the user just wanted this one button, I was trying
to duplicate Excel's ability to drag a button to a different place on the
toolbar and then be there the next time they opened Excel (the button is
created with Temporary:=True). So I am storing the index of the button and
a couple other bits of info in a sheet on the addin.

I thought about using the registry, and I had a reason for not doing it, but
now I think that's the way to go. I don't want to add another file for the
sake of a very small addin.

Doug

 

[Bob Phillips]

Doug,

If you are saving information about the addin, such as the size of a resized
form, the position of a toolbar, that is legitimate (even in my book <g>),
and the registry is a good place to do so (Jan Karel Pieterse does that with
NameManager), and with GetSetting and SaveSetting it is simple and not
locked down.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)