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

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" wrote in message
...
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,

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

Thanks,

Doug

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

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)

"Doug Glancy" wrote in message
...
Bob,

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

Thanks,

Doug

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

Or maybe in another file?

Doug Glancy wrote:

Bob,

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

Thanks,

Doug

"Bob Phillips" wrote in message
...
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" wrote in message
...
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



--

Dave Peterson

Just use a simple .ini file.

RBS

"Doug Glancy" wrote in message
...
Bob,

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

Thanks,

Doug

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

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

"RB Smissaert" wrote in message
...
Just use a simple .ini file.

RBS

"Doug Glancy" wrote in message
...
Bob,

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

Thanks,

Doug

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

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)

"Doug Glancy" wrote in message
...
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

"RB Smissaert" wrote in message
...
Just use a simple .ini file.

RBS

"Doug Glancy" wrote in message
...
Bob,

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

Thanks,

Doug

"Bob Phillips" wrote in message
...
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" wrote in message
...
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