How do I create an add-in and distribute it to others?
I have a toolbar associated with the add-in. The user wants the toolbar to
be invisible most of the time. When she wants to use the add-in -
approximately once a month, she will make the toolbar visible.
What is the best way to achieve this?
Thanks
Habib

http://support.microsoft.com/?id=167909
XL: Securing Visual Basic Code in Microsoft Excel

http://support.microsoft.com/?id=156942
XL97: How to Create an Add-in File in Microsoft Excel 97

http://support.microsoft.com/?id=211563
How to create an add-in file in Excel 2000

http://msdn.microsoft.com/library/en...xceladdins.asp

http://msdn.microsoft.com/library/en...exceladdin.asp

http://www.j-walk.com/ss/excel/tips/tip45.htm
Excel 97: Creating Add-Ins

You addin should delete any existing version of the toolbar and create a new
version when it is loaded. It should also mark it as temporary and delete it
in the beforeclose event. The creation can be done in the workbook_open
event of the addin.

If the user will only load the addin when they want to use it, then this
should be sufficient. If they will load the addin all the time, then it
depends on the user. In tools=Customize, they can select whether the bar
is visible or not. If that is too hard, then perhaps you could provide a
function in your addin that they can run from Tools=Macro=Macros or you
can add an additional menu choice in the tools menu.

--
Regards,
Tom Ogilvy



"HSalim[MVP]" wrote in message
...
How do I create an add-in and distribute it to others?
I have a toolbar associated with the add-in. The user wants the toolbar
to
be invisible most of the time. When she wants to use the add-in -
approximately once a month, she will make the toolbar visible.
What is the best way to achieve this?
Thanks
Habib

Tom,

Thanks for the super-fast reply, and for all the links.
I did find Jan Karel's site, and have been trying to follow his advice.

I cant put my finger on what I am doing wrong, but I think I am permanently
deleting the toolbar.
I've had to recreate my toolbar a few times now as I seem to delete it.
Is there a way to backup a toolbar?
I could create the toolbar in code like JKP does - perhaps that is the
answer.
How do I save the button images so that I can create the toolbar with the
images I want?

REgards
HAbib


"Tom Ogilvy" wrote in message
...
: http://support.microsoft.com/?id=167909
: XL: Securing Visual Basic Code in Microsoft Excel
:
: http://support.microsoft.com/?id=156942
: XL97: How to Create an Add-in File in Microsoft Excel 97
:
: http://support.microsoft.com/?id=211563
: How to create an add-in file in Excel 2000
:
: http://msdn.microsoft.com/library/en...xceladdins.asp
:
:
http://msdn.microsoft.com/library/en...exceladdin.asp
:
: http://www.j-walk.com/ss/excel/tips/tip45.htm
: Excel 97: Creating Add-Ins
:
: You addin should delete any existing version of the toolbar and create a
new
: version when it is loaded. It should also mark it as temporary and delete
it
: in the beforeclose event. The creation can be done in the workbook_open
: event of the addin.
:
: If the user will only load the addin when they want to use it, then this
: should be sufficient. If they will load the addin all the time, then it
: depends on the user. In tools=Customize, they can select whether the bar
: is visible or not. If that is too hard, then perhaps you could provide a
: function in your addin that they can run from Tools=Macro=Macros or you
: can add an additional menu choice in the tools menu.
:
: --
: Regards,
: Tom Ogilvy
:
:
:
: "HSalim[MVP]" wrote in message
: ...
: How do I create an add-in and distribute it to others?
: I have a toolbar associated with the add-in. The user wants the toolbar
: to
: be invisible most of the time. When she wants to use the add-in -
: approximately once a month, she will make the toolbar visible.
: What is the best way to achieve this?
: Thanks
: Habib
:
:
:
:

put the button images on a sheet (hidden perhaps) and copy them to the
clipboard. then use pasteface to use them on your toolbar.

I did recommend that you delete and create each time.

--
Regards,
Tom Ogilvy


"HSalim[MVP]" wrote in message
...
Tom,

Thanks for the super-fast reply, and for all the links.
I did find Jan Karel's site, and have been trying to follow his advice.

I cant put my finger on what I am doing wrong, but I think I am
permanently
deleting the toolbar.
I've had to recreate my toolbar a few times now as I seem to delete it.
Is there a way to backup a toolbar?
I could create the toolbar in code like JKP does - perhaps that is the
answer.
How do I save the button images so that I can create the toolbar with the
images I want?

REgards
HAbib


"Tom Ogilvy" wrote in message
...
: http://support.microsoft.com/?id=167909
: XL: Securing Visual Basic Code in Microsoft Excel
:
: http://support.microsoft.com/?id=156942
: XL97: How to Create an Add-in File in Microsoft Excel 97
:
: http://support.microsoft.com/?id=211563
: How to create an add-in file in Excel 2000
:
: http://msdn.microsoft.com/library/en...xceladdins.asp
:
:

http://msdn.microsoft.com/library/en...exceladdin.asp
:
: http://www.j-walk.com/ss/excel/tips/tip45.htm
: Excel 97: Creating Add-Ins
:
: You addin should delete any existing version of the toolbar and create a
new
: version when it is loaded. It should also mark it as temporary and
delete
it
: in the beforeclose event. The creation can be done in the
workbook_open
: event of the addin.
:
: If the user will only load the addin when they want to use it, then this
: should be sufficient. If they will load the addin all the time, then it
: depends on the user. In tools=Customize, they can select whether the
bar
: is visible or not. If that is too hard, then perhaps you could provide
a
: function in your addin that they can run from Tools=Macro=Macros or
you
: can add an additional menu choice in the tools menu.
:
: --
: Regards,
: Tom Ogilvy
:
:
:
: "HSalim[MVP]" wrote in message
: ...
: How do I create an add-in and distribute it to others?
: I have a toolbar associated with the add-in. The user wants the
toolbar
: to
: be invisible most of the time. When she wants to use the add-in -
: approximately once a month, she will make the toolbar visible.
: What is the best way to achieve this?
: Thanks
: Habib
:
:
:
:

Hi HAbib,

Referring to your OP perhaps you don't want to delete the toolbar when the
addin unloads. If so don't set the toolbar's Temporary property True and
don't delete in the file's close event.

With this arrangement it's NOT necessary to install the addin into the addin
manager (with code or with user Tools / Options / Addins…).

When user clicks on your toolbar if your addin is not already loaded it will
automatically load.

You could have an additional button linking to a routine to close the addin
if no longer required in the current session. Maybe also make the toolbar
invisible on unload, eg when Excel closes if not before.

In the open routine test if your toolbar exists, if not create it, if it
does check it is all correct. There are plenty of unused icons that if
suitable might avoid necessity of creating custom icons.

Advise user to simply double click the addin in it's folder first time to
load and create the toolbar. When user closes Excel there should be no other
running instances to ensure it gets stored in user's toolbar / *.xlb file).

This is certainly NOT the normal way to distribute an addin and could
antagonise users. However it might fit your user's particular requirements,
a) knows how to make the toolbar visible, b) infrequent use (addin only gets
loaded when specifically required), c) knows how to recover the toolbar if
it accidentally gets deleted (load addin from file).

Regards,
Peter T

"HSalim[MVP]" wrote in message
...
Tom,

Thanks for the super-fast reply, and for all the links.
I did find Jan Karel's site, and have been trying to follow his advice.

I cant put my finger on what I am doing wrong, but I think I am
permanently
deleting the toolbar.
I've had to recreate my toolbar a few times now as I seem to delete it.
Is there a way to backup a toolbar?
I could create the toolbar in code like JKP does - perhaps that is the
answer.
How do I save the button images so that I can create the toolbar with the
images I want?

REgards
HAbib


"Tom Ogilvy" wrote in message
...
: http://support.microsoft.com/?id=167909
: XL: Securing Visual Basic Code in Microsoft Excel
:
: http://support.microsoft.com/?id=156942
: XL97: How to Create an Add-in File in Microsoft Excel 97
:
: http://support.microsoft.com/?id=211563
: How to create an add-in file in Excel 2000
:
: http://msdn.microsoft.com/library/en...xceladdins.asp
:
:

http://msdn.microsoft.com/library/en...tingexceladdin.
asp
:
: http://www.j-walk.com/ss/excel/tips/tip45.htm
: Excel 97: Creating Add-Ins
:
: You addin should delete any existing version of the toolbar and create a
new
: version when it is loaded. It should also mark it as temporary and
delete
it
: in the beforeclose event. The creation can be done in the
workbook_open
: event of the addin.
:
: If the user will only load the addin when they want to use it, then this
: should be sufficient. If they will load the addin all the time, then it
: depends on the user. In tools=Customize, they can select whether the
bar
: is visible or not. If that is too hard, then perhaps you could provide
a
: function in your addin that they can run from Tools=Macro=Macros or
you
: can add an additional menu choice in the tools menu.
:
: --
: Regards,
: Tom Ogilvy
:
:
:
: "HSalim[MVP]" wrote in message
: ...
: How do I create an add-in and distribute it to others?
: I have a toolbar associated with the add-in. The user wants the
toolbar
: to
: be invisible most of the time. When she wants to use the add-in -
: approximately once a month, she will make the toolbar visible.
: What is the best way to achieve this?
: Thanks
: Habib
:
:
:
:

Tom and Peter,

Thanks for the assistance, and the KB links.

I have
a workbook (lockbox.xls)
a toolbar - also called Lockbox, with about 7 buttons. macros point to
routines in Lockbox.xls

If I convert the workbook to an add-in, - lockbox.xla the toolbar does not
seem to follow
along, it still points to the lockbox.xls.

If I convert lockbox.xls into a macro in the immediate window -
thisworkbook.isaddin = true
then the user gets a macro warning.

How can I make it all nicely integrated without generating warnings?

Thanks
Habib

"Peter T" <peter_t@discussions wrote in message
...
: Hi HAbib,
:
: Referring to your OP perhaps you don't want to delete the toolbar when the
: addin unloads. If so don't set the toolbar's Temporary property True and
: don't delete in the file's close event.
:
: With this arrangement it's NOT necessary to install the addin into the
addin
: manager (with code or with user Tools / Options / Addins.).
:
: When user clicks on your toolbar if your addin is not already loaded it
will
: automatically load.
:
: You could have an additional button linking to a routine to close the
addin
: if no longer required in the current session. Maybe also make the toolbar
: invisible on unload, eg when Excel closes if not before.
:
: In the open routine test if your toolbar exists, if not create it, if it
: does check it is all correct. There are plenty of unused icons that if
: suitable might avoid necessity of creating custom icons.
:
: Advise user to simply double click the addin in it's folder first time to
: load and create the toolbar. When user closes Excel there should be no
other
: running instances to ensure it gets stored in user's toolbar / *.xlb
file).
:
: This is certainly NOT the normal way to distribute an addin and could
: antagonise users. However it might fit your user's particular
requirements,
: a) knows how to make the toolbar visible, b) infrequent use (addin only
gets
: loaded when specifically required), c) knows how to recover the toolbar if
: it accidentally gets deleted (load addin from file).
:
: Regards,
: Peter T
:
: "HSalim[MVP]" wrote in message
: ...
: Tom,
:
: Thanks for the super-fast reply, and for all the links.
: I did find Jan Karel's site, and have been trying to follow his advice.
:
: I cant put my finger on what I am doing wrong, but I think I am
: permanently
: deleting the toolbar.
: I've had to recreate my toolbar a few times now as I seem to delete it.
: Is there a way to backup a toolbar?
: I could create the toolbar in code like JKP does - perhaps that is the
: answer.
: How do I save the button images so that I can create the toolbar with
the
: images I want?
:
: REgards
: HAbib
:
:
: "Tom Ogilvy" wrote in message
: ...
: : http://support.microsoft.com/?id=167909
: : XL: Securing Visual Basic Code in Microsoft Excel
: :
: : http://support.microsoft.com/?id=156942
: : XL97: How to Create an Add-in File in Microsoft Excel 97
: :
: : http://support.microsoft.com/?id=211563
: : How to create an add-in file in Excel 2000
: :
: :
http://msdn.microsoft.com/library/en...xceladdins.asp
: :
: :
:
:
http://msdn.microsoft.com/library/en...tingexceladdin.
: asp
: :
: : http://www.j-walk.com/ss/excel/tips/tip45.htm
: : Excel 97: Creating Add-Ins
: :
: : You addin should delete any existing version of the toolbar and create
a
: new
: : version when it is loaded. It should also mark it as temporary and
: delete
: it
: : in the beforeclose event. The creation can be done in the
: workbook_open
: : event of the addin.
: :
: : If the user will only load the addin when they want to use it, then
this
: : should be sufficient. If they will load the addin all the time, then
it
: : depends on the user. In tools=Customize, they can select whether the
: bar
: : is visible or not. If that is too hard, then perhaps you could
provide
: a
: : function in your addin that they can run from Tools=Macro=Macros or
: you
: : can add an additional menu choice in the tools menu.
: :
: : --
: : Regards,
: : Tom Ogilvy
: :
: :
: :
: : "HSalim[MVP]" wrote in message
: : ...
: : How do I create an add-in and distribute it to others?
: : I have a toolbar associated with the add-in. The user wants the
: toolbar
: : to
: : be invisible most of the time. When she wants to use the add-in -
: : approximately once a month, she will make the toolbar visible.
: : What is the best way to achieve this?
: : Thanks
: : Habib
: :
: :
: :
: :
:
:
:
:

Hi Habib,

Are you "Attaching" a toolbar, try creating one:

Paste the following into a new workbook.
File/SaveAs, in the "Save As type" dropdown scroll down to Microsoft Addin
and select, Save.

Can load from file first time to add the toolbar, thereafter click on
toolbar to load. But to avoid the macro warning (if Medium & in Trusted
sources - trust all installed addins checked) install as an Addin. If always
going to be installed as an addin then set the Temporary = True option when
creating the Toolbar.

Sub Auto_Open()
Dim cBar As CommandBar
Dim i As Long

On Error Resume Next
Set cBar = Application.CommandBars("MyTestBar")
On Error GoTo 0

If Not cBar Is Nothing Then

If cBar.Controls.Count < 2 Then
cBar.Delete
Set cBar = Nothing
End If
End If

If cBar Is Nothing Then
Set cBar = Application.CommandBars.Add(Name:="MyTestBar")
'or .Add.(Name:="MyTestBar", Temporary = True)
For i = 1 To 2
cBar.Controls.Add Type:=msoControlButton
Next i
End If

With cBar.Controls(1)
.OnAction = "Macro1"
.FaceId = 482
.TooltipText = "Macro1"
End With
With cBar.Controls(2)
.OnAction = "Macro2"
.FaceId = 483
.TooltipText = "Macro2"
End With

cBar.Enabled = True
cBar.Visible = True
End Sub

Sub Macro1()
MsgBox "Macro1"
End Sub
Sub Macro2()

If MsgBox("Close me", vbYesNo, "Macro2") = vbYes Then
Application.OnTime Now, "CloseMe"
End If
End Sub

Sub CloseMe()
ThisWorkbook.Close
End Sub

'' in thisworkbook module
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
'Application.CommandBars("MyTestBar").Delete

'or not installed as an addin
Application.CommandBars("MyTestBar").Visible = False

End Sub

Regards,
Peter T

"HSalim[MVP]" wrote in message
...
Tom and Peter,

Thanks for the assistance, and the KB links.

I have
a workbook (lockbox.xls)
a toolbar - also called Lockbox, with about 7 buttons. macros point to
routines in Lockbox.xls

If I convert the workbook to an add-in, - lockbox.xla the toolbar does not
seem to follow
along, it still points to the lockbox.xls.

If I convert lockbox.xls into a macro in the immediate window -
thisworkbook.isaddin = true
then the user gets a macro warning.

How can I make it all nicely integrated without generating warnings?

Thanks
Habib

"Peter T" <peter_t@discussions wrote in message
...
: Hi HAbib,
:
: Referring to your OP perhaps you don't want to delete the toolbar when
the
: addin unloads. If so don't set the toolbar's Temporary property True and
: don't delete in the file's close event.
:
: With this arrangement it's NOT necessary to install the addin into the
addin
: manager (with code or with user Tools / Options / Addins.).
:
: When user clicks on your toolbar if your addin is not already loaded it
will
: automatically load.
:
: You could have an additional button linking to a routine to close the
addin
: if no longer required in the current session. Maybe also make the
toolbar
: invisible on unload, eg when Excel closes if not before.
:
: In the open routine test if your toolbar exists, if not create it, if it
: does check it is all correct. There are plenty of unused icons that if
: suitable might avoid necessity of creating custom icons.
:
: Advise user to simply double click the addin in it's folder first time
to
: load and create the toolbar. When user closes Excel there should be no
other
: running instances to ensure it gets stored in user's toolbar / *.xlb
file).
:
: This is certainly NOT the normal way to distribute an addin and could
: antagonise users. However it might fit your user's particular
requirements,
: a) knows how to make the toolbar visible, b) infrequent use (addin only
gets
: loaded when specifically required), c) knows how to recover the toolbar
if
: it accidentally gets deleted (load addin from file).
:
: Regards,
: Peter T
:
: "HSalim[MVP]" wrote in message
: ...
: Tom,
:
: Thanks for the super-fast reply, and for all the links.
: I did find Jan Karel's site, and have been trying to follow his
advice.
:
: I cant put my finger on what I am doing wrong, but I think I am
: permanently
: deleting the toolbar.
: I've had to recreate my toolbar a few times now as I seem to delete
it.
: Is there a way to backup a toolbar?
: I could create the toolbar in code like JKP does - perhaps that is the
: answer.
: How do I save the button images so that I can create the toolbar with
the
: images I want?
:
: REgards
: HAbib
:
:
: "Tom Ogilvy" wrote in message
: ...
: : http://support.microsoft.com/?id=167909
: : XL: Securing Visual Basic Code in Microsoft Excel
: :
: : http://support.microsoft.com/?id=156942
: : XL97: How to Create an Add-in File in Microsoft Excel 97
: :
: : http://support.microsoft.com/?id=211563
: : How to create an add-in file in Excel 2000
: :
: :
http://msdn.microsoft.com/library/en...xceladdins.asp
: :
: :
:
:

http://msdn.microsoft.com/library/en...tingexceladdin.
: asp
: :
: : http://www.j-walk.com/ss/excel/tips/tip45.htm
: : Excel 97: Creating Add-Ins
: :
: : You addin should delete any existing version of the toolbar and
create
a
: new
: : version when it is loaded. It should also mark it as temporary and
: delete
: it
: : in the beforeclose event. The creation can be done in the
: workbook_open
: : event of the addin.
: :
: : If the user will only load the addin when they want to use it, then
this
: : should be sufficient. If they will load the addin all the time,
then
it
: : depends on the user. In tools=Customize, they can select whether
the
: bar
: : is visible or not. If that is too hard, then perhaps you could
provide
: a
: : function in your addin that they can run from Tools=Macro=Macros
or
: you
: : can add an additional menu choice in the tools menu.
: :
: : --
: : Regards,
: : Tom Ogilvy
: :
: :
: :
: : "HSalim[MVP]" wrote in message
: : ...
: : How do I create an add-in and distribute it to others?
: : I have a toolbar associated with the add-in. The user wants the
: toolbar
: : to
: : be invisible most of the time. When she wants to use the add-in -
: : approximately once a month, she will make the toolbar visible.
: : What is the best way to achieve this?
: : Thanks
: : Habib
: :
: :
: :
: :
:
:
:
:

I see that you have already provided the answer to my latest question in
your first post on this subject.
I don't need to close the add-in - that will happen when the user quits
excel at the end of the day.
Ok, I won't delete the toolbar on close, so the addin loads when the user
clicks on one of the toolbar buttons.

All good, but why do you say this is non-standard? and what would be a
better way to distribute this add-in?

Again, thanks the help.

Regards
Habib

"Peter T" <peter_t@discussions wrote in message
...
: Hi HAbib,
:
: Referring to your OP perhaps you don't want to delete the toolbar when the
: addin unloads. If so don't set the toolbar's Temporary property True and
: don't delete in the file's close event.
:
: With this arrangement it's NOT necessary to install the addin into the
addin
: manager (with code or with user Tools / Options / Addins.).
:
: When user clicks on your toolbar if your addin is not already loaded it
will
: automatically load.
:
: You could have an additional button linking to a routine to close the
addin
: if no longer required in the current session. Maybe also make the toolbar
: invisible on unload, eg when Excel closes if not before.
:
: In the open routine test if your toolbar exists, if not create it, if it
: does check it is all correct. There are plenty of unused icons that if
: suitable might avoid necessity of creating custom icons.
:
: Advise user to simply double click the addin in it's folder first time to
: load and create the toolbar. When user closes Excel there should be no
other
: running instances to ensure it gets stored in user's toolbar / *.xlb
file).
:
: This is certainly NOT the normal way to distribute an addin and could
: antagonise users. However it might fit your user's particular
requirements,
: a) knows how to make the toolbar visible, b) infrequent use (addin only
gets
: loaded when specifically required), c) knows how to recover the toolbar if
: it accidentally gets deleted (load addin from file).
:
: Regards,
: Peter T
:
: "HSalim[MVP]" wrote in message
: ...
: Tom,
:
: Thanks for the super-fast reply, and for all the links.
: I did find Jan Karel's site, and have been trying to follow his advice.
:
: I cant put my finger on what I am doing wrong, but I think I am
: permanently
: deleting the toolbar.
: I've had to recreate my toolbar a few times now as I seem to delete it.
: Is there a way to backup a toolbar?
: I could create the toolbar in code like JKP does - perhaps that is the
: answer.
: How do I save the button images so that I can create the toolbar with
the
: images I want?
:
: REgards
: HAbib
:
:
: "Tom Ogilvy" wrote in message
: ...
: : http://support.microsoft.com/?id=167909
: : XL: Securing Visual Basic Code in Microsoft Excel
: :
: : http://support.microsoft.com/?id=156942
: : XL97: How to Create an Add-in File in Microsoft Excel 97
: :
: : http://support.microsoft.com/?id=211563
: : How to create an add-in file in Excel 2000
: :
: :
http://msdn.microsoft.com/library/en...xceladdins.asp
: :
: :
:
:
http://msdn.microsoft.com/library/en...tingexceladdin.
: asp
: :
: : http://www.j-walk.com/ss/excel/tips/tip45.htm
: : Excel 97: Creating Add-Ins
: :
: : You addin should delete any existing version of the toolbar and create
a
: new
: : version when it is loaded. It should also mark it as temporary and
: delete
: it
: : in the beforeclose event. The creation can be done in the
: workbook_open
: : event of the addin.
: :
: : If the user will only load the addin when they want to use it, then
this
: : should be sufficient. If they will load the addin all the time, then
it
: : depends on the user. In tools=Customize, they can select whether the
: bar
: : is visible or not. If that is too hard, then perhaps you could
provide
: a
: : function in your addin that they can run from Tools=Macro=Macros or
: you
: : can add an additional menu choice in the tools menu.
: :
: : --
: : Regards,
: : Tom Ogilvy
: :
: :
: :
: : "HSalim[MVP]" wrote in message
: : ...
: : How do I create an add-in and distribute it to others?
: : I have a toolbar associated with the add-in. The user wants the
: toolbar
: : to
: : be invisible most of the time. When she wants to use the add-in -
: : approximately once a month, she will make the toolbar visible.
: : What is the best way to achieve this?
: : Thanks
: : Habib
: :
: :
: :
: :
:
:
:
: