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
: :
: :
: :
: :
:
:
:
:

Peter,
We are getting somewhere now !

I was fighting the idea of creating a toolbar at run-time but I actually am
beginning to like it.
In the worksheet (which will get hidden) Ihave a named range called buttons
where I can list all the buttons that will be created on Auto_Open. - here
is the relevant code-snippet.

Range("Buttons").Select
For Each cell In Selection
cBar.Controls.Add Type:=msoControlButton
With cBar.Controls(cell.Value)
.OnAction = cell.Offset(, 1).Value
.FaceId = cell.Offset(, 2).Value
.Caption = cell.Offset(, 3).Value
.TooltipText = cell.Offset(, 4).Value
.Style = msoButtonCaption 'was msoButtonIconAndCaption

End With
Next


If I edit a built-in button image (face) the faceId becomes 0. I know it is
being stored somewhere - i can copy the picture and the mask and even save
it to a file but I can't seem to access it.

Or at least, there does not seem to be an elegant way to access it. I dont
want to dostribute a bunch of image files with my add-in, so I guess I'll
just keep it simple and make it a text toolbar.

Thanks for all your help.

Regards
Habib





"Peter T" <peter_t@discussions wrote in message
...
: 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
: : :
: : :
: : :
: : :
: :
: :
: :
: :
:
:
:
:
:

Hope somewhere is in the right direction! Couple of points -

If your file is an addin all worksheets are of course hidden. Suggest don't
use Select, rarely necessary and particularly not in a non-active sheet.
Change -

Range("Buttons").Select
For Each cell In Selection

(I prefer not to use Each "Cell" which is a keyword)

Dim cel as Range
For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")

Why not store your icons, assuming you want to use them, as "Pictures" with
their names in your Buttons range

ThisWorkbook.Worksheets.Shapes(cel.Offset(, 5).Value).Copypicture
..PasteFace

Regards,
Peter T


"HSalim[MVP]" wrote in message
...
Peter,
We are getting somewhere now !

I was fighting the idea of creating a toolbar at run-time but I actually
am
beginning to like it.
In the worksheet (which will get hidden) Ihave a named range called
buttons
where I can list all the buttons that will be created on Auto_Open. - here
is the relevant code-snippet.

Range("Buttons").Select
For Each cell In Selection
cBar.Controls.Add Type:=msoControlButton
With cBar.Controls(cell.Value)
.OnAction = cell.Offset(, 1).Value
.FaceId = cell.Offset(, 2).Value
.Caption = cell.Offset(, 3).Value
.TooltipText = cell.Offset(, 4).Value
.Style = msoButtonCaption 'was msoButtonIconAndCaption

End With
Next


If I edit a built-in button image (face) the faceId becomes 0. I know it
is
being stored somewhere - i can copy the picture and the mask and even save
it to a file but I can't seem to access it.

Or at least, there does not seem to be an elegant way to access it. I
dont
want to dostribute a bunch of image files with my add-in, so I guess I'll
just keep it simple and make it a text toolbar.

Thanks for all your help.

Regards
Habib





"Peter T" <peter_t@discussions wrote in message
...
: 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
: : :
: : :
: : :
: : :
: :
: :
: :
: :
:
:
:
:
:

Typo -

ThisWorkbook.Worksheets.Shapes(cel.Offset(, 5).Value).Copypicture

ThisWorkbook.Worksheets("Sheet1").Shapes(cel.Offse t(, 5).Value).Copypicture

Peter T


"Peter T" <peter_t@discussions wrote in message
...
Hope somewhere is in the right direction! Couple of points -

If your file is an addin all worksheets are of course hidden. Suggest
don't
use Select, rarely necessary and particularly not in a non-active sheet.
Change -

Range("Buttons").Select
For Each cell In Selection

(I prefer not to use Each "Cell" which is a keyword)

Dim cel as Range
For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")

Why not store your icons, assuming you want to use them, as "Pictures"
with
their names in your Buttons range

ThisWorkbook.Worksheets.Shapes(cel.Offset(, 5).Value).Copypicture
.PasteFace

Regards,
Peter T


"HSalim[MVP]" wrote in message
...
Peter,
We are getting somewhere now !

I was fighting the idea of creating a toolbar at run-time but I actually
am
beginning to like it.
In the worksheet (which will get hidden) Ihave a named range called
buttons
where I can list all the buttons that will be created on Auto_Open. -
here
is the relevant code-snippet.

Range("Buttons").Select
For Each cell In Selection
cBar.Controls.Add Type:=msoControlButton
With cBar.Controls(cell.Value)
.OnAction = cell.Offset(, 1).Value
.FaceId = cell.Offset(, 2).Value
.Caption = cell.Offset(, 3).Value
.TooltipText = cell.Offset(, 4).Value
.Style = msoButtonCaption 'was msoButtonIconAndCaption

End With
Next


If I edit a built-in button image (face) the faceId becomes 0. I know it
is
being stored somewhere - i can copy the picture and the mask and even
save
it to a file but I can't seem to access it.

Or at least, there does not seem to be an elegant way to access it. I
dont
want to dostribute a bunch of image files with my add-in, so I guess
I'll
just keep it simple and make it a text toolbar.

Thanks for all your help.

Regards
Habib





"Peter T" <peter_t@discussions wrote in message
...
: 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
: : :
: : :
: : :
: : :
: :
: :
: :
: :
:
:
:
:
:

Right direction is right!
Thanks for the reply - that is exactly what I wanted and did not know how to
do.
- I'll try it out right now.

I was trying to find ways to do this. I found out that each buttonface can
has a picture and a mask property, both if which need to be supplied - and I
did not want to distribute 16 image files with my add-in.

Thanks also for the cel/select tip.

Regards
Habib



"Peter T" <peter_t@discussions wrote in message
...
: Hope somewhere is in the right direction! Couple of points -
:
: If your file is an addin all worksheets are of course hidden. Suggest
don't
: use Select, rarely necessary and particularly not in a non-active sheet.
: Change -
:
: Range("Buttons").Select
: For Each cell In Selection
:
: (I prefer not to use Each "Cell" which is a keyword)
:
: Dim cel as Range
: For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
:
: Why not store your icons, assuming you want to use them, as "Pictures"
with
: their names in your Buttons range
:
: ThisWorkbook.Worksheets.Shapes(cel.Offset(, 5).Value).Copypicture
: .PasteFace
:
: Regards,
: Peter T
:
:
: "HSalim[MVP]" wrote in message
: ...
: Peter,
: We are getting somewhere now !
:
: I was fighting the idea of creating a toolbar at run-time but I actually
: am
: beginning to like it.
: In the worksheet (which will get hidden) Ihave a named range called
: buttons
: where I can list all the buttons that will be created on Auto_Open. -
here
: is the relevant code-snippet.
:
: Range("Buttons").Select
: For Each cell In Selection
: cBar.Controls.Add Type:=msoControlButton
: With cBar.Controls(cell.Value)
: .OnAction = cell.Offset(, 1).Value
: .FaceId = cell.Offset(, 2).Value
: .Caption = cell.Offset(, 3).Value
: .TooltipText = cell.Offset(, 4).Value
: .Style = msoButtonCaption 'was msoButtonIconAndCaption
:
: End With
: Next
:
:
: If I edit a built-in button image (face) the faceId becomes 0. I know it
: is
: being stored somewhere - i can copy the picture and the mask and even
save
: it to a file but I can't seem to access it.
:
: Or at least, there does not seem to be an elegant way to access it. I
: dont
: want to dostribute a bunch of image files with my add-in, so I guess
I'll
: just keep it simple and make it a text toolbar.
:
: Thanks for all your help.
:
: Regards
: Habib
:
:
:
:
:
: "Peter T" <peter_t@discussions wrote in message
: ...
: : 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
: : : :
: : : :
: : : :
: : : :
: : :
: : :
: : :
: : :
: :
: :
: :
: :
: :
:
:
:
:

That was a long trip around the block. <g

--
Regards,
Tom Ogilvy


"HSalim[MVP]" wrote in message
...
Right direction is right!
Thanks for the reply - that is exactly what I wanted and did not know how
to
do.
- I'll try it out right now.

I was trying to find ways to do this. I found out that each buttonface
can
has a picture and a mask property, both if which need to be supplied - and
I
did not want to distribute 16 image files with my add-in.

Thanks also for the cel/select tip.

Regards
Habib



"Peter T" <peter_t@discussions wrote in message
...
: Hope somewhere is in the right direction! Couple of points -
:
: If your file is an addin all worksheets are of course hidden. Suggest
don't
: use Select, rarely necessary and particularly not in a non-active sheet.
: Change -
:
: Range("Buttons").Select
: For Each cell In Selection
:
: (I prefer not to use Each "Cell" which is a keyword)
:
: Dim cel as Range
: For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
:
: Why not store your icons, assuming you want to use them, as "Pictures"
with
: their names in your Buttons range
:
: ThisWorkbook.Worksheets.Shapes(cel.Offset(, 5).Value).Copypicture
: .PasteFace
:
: Regards,
: Peter T
:
:
: "HSalim[MVP]" wrote in message
: ...
: Peter,
: We are getting somewhere now !
:
: I was fighting the idea of creating a toolbar at run-time but I
actually
: am
: beginning to like it.
: In the worksheet (which will get hidden) Ihave a named range called
: buttons
: where I can list all the buttons that will be created on Auto_Open. -
here
: is the relevant code-snippet.
:
: Range("Buttons").Select
: For Each cell In Selection
: cBar.Controls.Add Type:=msoControlButton
: With cBar.Controls(cell.Value)
: .OnAction = cell.Offset(, 1).Value
: .FaceId = cell.Offset(, 2).Value
: .Caption = cell.Offset(, 3).Value
: .TooltipText = cell.Offset(, 4).Value
: .Style = msoButtonCaption 'was msoButtonIconAndCaption
:
: End With
: Next
:
:
: If I edit a built-in button image (face) the faceId becomes 0. I know
it
: is
: being stored somewhere - i can copy the picture and the mask and even
save
: it to a file but I can't seem to access it.
:
: Or at least, there does not seem to be an elegant way to access it. I
: dont
: want to dostribute a bunch of image files with my add-in, so I guess
I'll
: just keep it simple and make it a text toolbar.
:
: Thanks for all your help.
:
: Regards
: Habib
:
:
:
:
:
: "Peter T" <peter_t@discussions wrote in message
: ...
: : 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
: : : :
: : : :
: : : :
: : : :
: : :
: : :
: : :
: : :
: :
: :
: :
: :
: :
:
:
:
:

You might want to look at KeepItCool's post on working with the bitmap and
mask:

http://tinyurl.com/cdb9m

--
Regards,
Tom Ogilvy


"HSalim[MVP]" wrote in message
...
Right direction is right!
Thanks for the reply - that is exactly what I wanted and did not know how
to
do.
- I'll try it out right now.

I was trying to find ways to do this. I found out that each buttonface
can
has a picture and a mask property, both if which need to be supplied - and
I
did not want to distribute 16 image files with my add-in.

Thanks also for the cel/select tip.

Regards
Habib



"Peter T" <peter_t@discussions wrote in message
...
: Hope somewhere is in the right direction! Couple of points -
:
: If your file is an addin all worksheets are of course hidden. Suggest
don't
: use Select, rarely necessary and particularly not in a non-active sheet.
: Change -
:
: Range("Buttons").Select
: For Each cell In Selection
:
: (I prefer not to use Each "Cell" which is a keyword)
:
: Dim cel as Range
: For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
:
: Why not store your icons, assuming you want to use them, as "Pictures"
with
: their names in your Buttons range
:
: ThisWorkbook.Worksheets.Shapes(cel.Offset(, 5).Value).Copypicture
: .PasteFace
:
: Regards,
: Peter T
:
:
: "HSalim[MVP]" wrote in message
: ...
: Peter,
: We are getting somewhere now !
:
: I was fighting the idea of creating a toolbar at run-time but I
actually
: am
: beginning to like it.
: In the worksheet (which will get hidden) Ihave a named range called
: buttons
: where I can list all the buttons that will be created on Auto_Open. -
here
: is the relevant code-snippet.
:
: Range("Buttons").Select
: For Each cell In Selection
: cBar.Controls.Add Type:=msoControlButton
: With cBar.Controls(cell.Value)
: .OnAction = cell.Offset(, 1).Value
: .FaceId = cell.Offset(, 2).Value
: .Caption = cell.Offset(, 3).Value
: .TooltipText = cell.Offset(, 4).Value
: .Style = msoButtonCaption 'was msoButtonIconAndCaption
:
: End With
: Next
:
:
: If I edit a built-in button image (face) the faceId becomes 0. I know
it
: is
: being stored somewhere - i can copy the picture and the mask and even
save
: it to a file but I can't seem to access it.
:
: Or at least, there does not seem to be an elegant way to access it. I
: dont
: want to dostribute a bunch of image files with my add-in, so I guess
I'll
: just keep it simple and make it a text toolbar.
:
: Thanks for all your help.
:
: Regards
: Habib
:
:
:
:
:
: "Peter T" <peter_t@discussions wrote in message
: ...
: : 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
: : : :
: : : :
: : : :
: : : :
: : :
: : :
: : :
: : :
: :
: :
: :
: :
: :
:
:
:
:

Right direction is right!

Great!

Thanks for the reply - that is exactly what I wanted and did not know how
to
do.
- I'll try it out right now.

I was trying to find ways to do this. I found out that each buttonface
can
has a picture and a mask property, both if which need to be supplied - and
I
did not want to distribute 16 image files with my add-in.

<snip

I don't think 16 small image files overall would make a larger file size
than attaching same on a toolbar. As regards applying the picture and the
mask you might be interested to read KeepITCool's technique in this thread.

http://tinyurl.com/7d2fu

Regards,
Peter T

Tom
That is just amazing! I just gave up hunting around in the help files, and
was going to ask for more help when Voila! that answer is waiting for me.

Thanks.
Habib



"Tom Ogilvy" wrote in message
...
: You might want to look at KeepItCool's post on working with the bitmap and
: mask:
:
: http://tinyurl.com/cdb9m
:
: --
: Regards,
: Tom Ogilvy
:
:
: "HSalim[MVP]" wrote in message
: ...
: Right direction is right!
: Thanks for the reply - that is exactly what I wanted and did not know
how
: to
: do.
: - I'll try it out right now.
:
: I was trying to find ways to do this. I found out that each buttonface
: can
: has a picture and a mask property, both if which need to be supplied -
and
: I
: did not want to distribute 16 image files with my add-in.
:
: Thanks also for the cel/select tip.
:
: Regards
: Habib
:
:
:
: "Peter T" <peter_t@discussions wrote in message
: ...
: : Hope somewhere is in the right direction! Couple of points -
: :
: : If your file is an addin all worksheets are of course hidden. Suggest
: don't
: : use Select, rarely necessary and particularly not in a non-active
sheet.
: : Change -
: :
: : Range("Buttons").Select
: : For Each cell In Selection
: :
: : (I prefer not to use Each "Cell" which is a keyword)
: :
: : Dim cel as Range
: : For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
: :
: : Why not store your icons, assuming you want to use them, as "Pictures"
: with
: : their names in your Buttons range
: :
: : ThisWorkbook.Worksheets.Shapes(cel.Offset(, 5).Value).Copypicture
: : .PasteFace
: :
: : Regards,
: : Peter T
: :
: :
: : "HSalim[MVP]" wrote in message
: : ...
: : Peter,
: : We are getting somewhere now !
: :
: : I was fighting the idea of creating a toolbar at run-time but I
: actually
: : am
: : beginning to like it.
: : In the worksheet (which will get hidden) Ihave a named range called
: : buttons
: : where I can list all the buttons that will be created on
Auto_Open. -
: here
: : is the relevant code-snippet.
: :
: : Range("Buttons").Select
: : For Each cell In Selection
: : cBar.Controls.Add Type:=msoControlButton
: : With cBar.Controls(cell.Value)
: : .OnAction = cell.Offset(, 1).Value
: : .FaceId = cell.Offset(, 2).Value
: : .Caption = cell.Offset(, 3).Value
: : .TooltipText = cell.Offset(, 4).Value
: : .Style = msoButtonCaption 'was
msoButtonIconAndCaption
: :
: : End With
: : Next
: :
: :
: : If I edit a built-in button image (face) the faceId becomes 0. I
know
: it
: : is
: : being stored somewhere - i can copy the picture and the mask and
even
: save
: : it to a file but I can't seem to access it.
: :
: : Or at least, there does not seem to be an elegant way to access it.
I
: : dont
: : want to dostribute a bunch of image files with my add-in, so I guess
: I'll
: : just keep it simple and make it a text toolbar.
: :
: : Thanks for all your help.
: :
: : Regards
: : Habib
: :
: :
: :
: :
: :
: : "Peter T" <peter_t@discussions wrote in message
: : ...
: : : 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
: : : : :
: : : : :
: : : : :
: : : : :
: : : :
: : : :
: : : :
: : : :
: : :
: : :
: : :
: : :
: : :
: :
: :
: :
: :
:
:
:
:

Peter,
It was not the file size that concerened me.
I just did not like the idea of having those image files outside - now I had
to be converned with handling errors if those files were deleted - troubles
with ensuring a consistent user experience etc...

Looks like you have a bit of experience in this area, huh!
Thanks for the help.

Regards
Habib




"Peter T" <peter_t@discussions wrote in message
...
: Right direction is right!
:
: Great!
:
: Thanks for the reply - that is exactly what I wanted and did not know
how
: to
: do.
: - I'll try it out right now.
:
: I was trying to find ways to do this. I found out that each buttonface
: can
: has a picture and a mask property, both if which need to be supplied -
and
: I
: did not want to distribute 16 image files with my add-in.
:
: <snip
:
: I don't think 16 small image files overall would make a larger file size
: than attaching same on a toolbar. As regards applying the picture and the
: mask you might be interested to read KeepITCool's technique in this
thread.
:
: http://tinyurl.com/7d2fu
:
: Regards,
: Peter T
:
:

Finally got it working. It is not quite waht I expected to see .
I never did get the setIcon routine to work, not did the .Picture and .Mask
methods work

This is what I did.
1. added a new Picturename column to my template.
2. Pasted the button image as a picture in the worksheet
3. Asigned a name for each picture

It appears that I can copy the image to memory and the paste the button
image on to the new button using .paste so here is the code snippet.

Regards
Habib



'---------------------
Sub Auto_Open()
Dim cBar As CommandBar
Dim i As Long, cel As Range
Dim ob As Office.CommandBarButton
Dim ws As Worksheet

'On Error GoTo 0
'On Error Resume Next
Set ws = Worksheets(1)

Set cBar = Application.CommandBars("LockBox")
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:="LockBox")
cBar.Visible = True

For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
cBar.Controls.Add Type:=msoControlButton
With cBar.Controls(cel.Value)
.OnAction = cel.Offset(, 1).Value
.Caption = cel.Offset(, 2).Value
.TooltipText = cel.Offset(, 3).Value
.Style = msoButtonIconAndCaption
ws.Shapes(cel.Offset(, 4).Value).CopyPicture xlScreen,
xlBitmap
.PasteFace
.BeginGroup = True
End With
Next
End If

cBar.Enabled = True
cBar.Position = msoBarTop
End Sub

'--------------------------------------

"HSalim[MVP]" wrote in message
...
: Peter,
: It was not the file size that concerened me.
: I just did not like the idea of having those image files outside - now I
had
: to be converned with handling errors if those files were deleted -
troubles
: with ensuring a consistent user experience etc...
:
: Looks like you have a bit of experience in this area, huh!
: Thanks for the help.
:
: Regards
: Habib
:
: :

Looks like you're there! To be ultra picky a couple more points -

Set ws = Worksheets(1)

I assume you are testing in an xls that becomes active when it opens, unlike
an xla. So you would need to qualify your Worksheet with the workbook.
However if it's an xls, if user had moved the original Worksheets(1) it will
refer to wrong sheet. If an xls use sheet-name (though in the pasted code
"ws" is not used).

Set ws = ThisWorkbook.Worksheets("Sheet1")

and later in the code
For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
can be changed to
For Each cel In ws.Range("Buttons")


Set cBar = Application.CommandBars("LockBox")
should be preceded with "On error Resume Next" in case the bar does not
exist.

This line from my previous suggestion
' If cBar.Controls.Count < 2 Then
was intended if the bar exists but without at least the number of expected
controls (eg 2), then delete the bar and make a new one.

I see you are deleting the bar if it exists and creating a new one each
time. This is a normal thing to do particularly if the bar should have been
deleted on close. However if you are leaving it place on close, originally
you said user wants to keep it, then don't delete the bar. User may have
positioned it to preference.

I'm not sure if you are always going to create a new bar or only if needs.
If the latter might be an idea to move
cBar.Visible = True
to after the section starting "If cBar Is Nothing Then", ie ensure it's
visible if you are not creating a new bar. However no harm to recreate the
controls each time even if the bar exists, eg

If cBar Is Nothing Then
Set cBar = Application.CommandBars.Add(Name:="LockBox")
End If
' create the controls and set bar properties

If your icons look OK I wouldn't worry about the additional code for Picture
& Mask, what you have will work in all versions. I think how "sharp" they
appear depends both on design and "type" of colours.

Regards,
Peter T

PS When I posted the link to KeepITcools method I didn't see Tom's slightly
earlier post with same.

"HSalim[MVP]" wrote in message
...
Finally got it working. It is not quite waht I expected to see .
I never did get the setIcon routine to work, not did the .Picture and
..Mask
methods work

This is what I did.
1. added a new Picturename column to my template.
2. Pasted the button image as a picture in the worksheet
3. Asigned a name for each picture

It appears that I can copy the image to memory and the paste the button
image on to the new button using .paste so here is the code snippet.

Regards
Habib



'---------------------
Sub Auto_Open()
Dim cBar As CommandBar
Dim i As Long, cel As Range
Dim ob As Office.CommandBarButton
Dim ws As Worksheet

'On Error GoTo 0
'On Error Resume Next
Set ws = Worksheets(1)

Set cBar = Application.CommandBars("LockBox")
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:="LockBox")
cBar.Visible = True

For Each cel In
ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
cBar.Controls.Add Type:=msoControlButton
With cBar.Controls(cel.Value)
.OnAction = cel.Offset(, 1).Value
.Caption = cel.Offset(, 2).Value
.TooltipText = cel.Offset(, 3).Value
.Style = msoButtonIconAndCaption
ws.Shapes(cel.Offset(, 4).Value).CopyPicture xlScreen,
xlBitmap
.PasteFace
.BeginGroup = True
End With
Next
End If

cBar.Enabled = True
cBar.Position = msoBarTop
End Sub

'--------------------------------------

"HSalim[MVP]" wrote in message
...
: Peter,
: It was not the file size that concerened me.
: I just did not like the idea of having those image files outside - now I
had
: to be converned with handling errors if those files were deleted -
troubles
: with ensuring a consistent user experience etc...
:
: Looks like you have a bit of experience in this area, huh!
: Thanks for the help.
:
: Regards
: Habib
:
: :

Peter,
Thanks for the suggestions - you and Tom have been a great help.
I will incorporate your suggestions.
re. On error Resume Next:
I'll check for the existence of the toolbar in a subroutine
(I feel that on error resume next should only be used in a tight little
routine that specifically checks for the anticipated error). I guess I have
been burned by that statement in the past.

I like your suggestion that the user may have positioned the bar elsewhere
so I'll adopt that, but that brings up another problem:

When does the add-in close? when the user exits Excel, right? And it loads
at excel start up, right? I am not sure how much memory each add-in
consumes and it might be a good idea to just start the add-in when it is
needed that one time a month. Your thoughts on that would be most welcome.

Regards
Habib






"Peter T" <peter_t@discussions wrote in message
...
: Looks like you're there! To be ultra picky a couple more points -
:
: Set ws = Worksheets(1)
:
: I assume you are testing in an xls that becomes active when it opens,
unlike
: an xla. So you would need to qualify your Worksheet with the workbook.
: However if it's an xls, if user had moved the original Worksheets(1) it
will
: refer to wrong sheet. If an xls use sheet-name (though in the pasted code
: "ws" is not used).
:
: Set ws = ThisWorkbook.Worksheets("Sheet1")
:
: and later in the code
: For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
: can be changed to
: For Each cel In ws.Range("Buttons")
:
:
: Set cBar = Application.CommandBars("LockBox")
: should be preceded with "On error Resume Next" in case the bar does not
: exist.
:
: This line from my previous suggestion
: ' If cBar.Controls.Count < 2 Then
: was intended if the bar exists but without at least the number of expected
: controls (eg 2), then delete the bar and make a new one.
:
: I see you are deleting the bar if it exists and creating a new one each
: time. This is a normal thing to do particularly if the bar should have
been
: deleted on close. However if you are leaving it place on close, originally
: you said user wants to keep it, then don't delete the bar. User may have
: positioned it to preference.
:
: I'm not sure if you are always going to create a new bar or only if needs.
: If the latter might be an idea to move
: cBar.Visible = True
: to after the section starting "If cBar Is Nothing Then", ie ensure it's
: visible if you are not creating a new bar. However no harm to recreate the
: controls each time even if the bar exists, eg
:
: If cBar Is Nothing Then
: Set cBar = Application.CommandBars.Add(Name:="LockBox")
: End If
: ' create the controls and set bar properties
:
: If your icons look OK I wouldn't worry about the additional code for
Picture
: & Mask, what you have will work in all versions. I think how "sharp" they
: appear depends both on design and "type" of colours.
:
: Regards,
: Peter T
:
: PS When I posted the link to KeepITcools method I didn't see Tom's
slightly
: earlier post with same.
:
: "HSalim[MVP]" wrote in message
: ...
: Finally got it working. It is not quite waht I expected to see .
: I never did get the setIcon routine to work, not did the .Picture and
: .Mask
: methods work
:
: This is what I did.
: 1. added a new Picturename column to my template.
: 2. Pasted the button image as a picture in the worksheet
: 3. Asigned a name for each picture
:
: It appears that I can copy the image to memory and the paste the button
: image on to the new button using .paste so here is the code snippet.
:
: Regards
: Habib
:
:
:
: '---------------------
: Sub Auto_Open()
: Dim cBar As CommandBar
: Dim i As Long, cel As Range
: Dim ob As Office.CommandBarButton
: Dim ws As Worksheet
:
: 'On Error GoTo 0
: 'On Error Resume Next
: Set ws = Worksheets(1)
:
: Set cBar = Application.CommandBars("LockBox")
: 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:="LockBox")
: cBar.Visible = True
:
: For Each cel In
: ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
: cBar.Controls.Add Type:=msoControlButton
: With cBar.Controls(cel.Value)
: .OnAction = cel.Offset(, 1).Value
: .Caption = cel.Offset(, 2).Value
: .TooltipText = cel.Offset(, 3).Value
: .Style = msoButtonIconAndCaption
: ws.Shapes(cel.Offset(, 4).Value).CopyPicture xlScreen,
: xlBitmap
: .PasteFace
: .BeginGroup = True
: End With
: Next
: End If
:
: cBar.Enabled = True
: cBar.Position = msoBarTop
: End Sub
:
: '--------------------------------------
:
: "HSalim[MVP]" wrote in message
: ...
: : Peter,
: : It was not the file size that concerened me.
: : I just did not like the idea of having those image files outside - now
I
: had
: : to be converned with handling errors if those files were deleted -
: troubles
: : with ensuring a consistent user experience etc...
: :
: : Looks like you have a bit of experience in this area, huh!
: : Thanks for the help.
: :
: : Regards
: : 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
: :
: :
: :
: :
:
:
:
:

Still at it <g

(I feel that on error resume next should only be used in a tight little
routine that specifically checks for the anticipated error).

I totally agree, but in this case we are anticipating an error. Immediately
after you could
On Error Goto 0
or
On Error Goto errH
'code
Exit Sub
errH:
Test why the error occurred, maybe "Resume" to another section. Not a lot to
go wrong but never can be 100% sure.
End Sub

When does the add-in close? , right?

The Addin will always unload, when the user exits Excel, if user uninstalls
from the Addin Manager by unchecking (if in the Addin's collection) or if
closed some other way - eg in the "Macro2" I posted earlier. Whatever way it
closes a close event will fire (assuming .EnableEvents has not been disabled
for any reason)

And it loads at excel start up, right?

Only if the Addin is installed in the Add manager and checked (Tools /
Addins..) or if the addin is in the XLStart folder, or loaded by some other
addin in it's open event (I assume unlikely).

I am not sure how much memory each add-in
consumes and it might be a good idea to just start the add-in when it is
needed that one time a month. Your thoughts on that would be most
welcome.

See my first post in this thread. It's my personal preference not to
"install" infrequently used addins, but either to leave an invisible custom
toolbar or leave a single menu item on one of Excel's toolbars (ie don't
delete it on unload). You say your user is familiar with how to activate an
invisible custom toolbar and only needs the addin once/mth. User can choose
either way - ie install as an addin or first time load from file (but don't
delete the toolbar in the close event).

In the close event you could test if your xla is an installed addin, if so
delete the toolbar, if not leave it in place.

As to how much memory, that of course is relative to the size of your addin,
how much on sheets, how much code, state of compile and several other
things. With modern systems I suppose typical addins are not much of an
issue in this respect. Having said that I'm often amazed at how my old low
spec system appears to run faster than some much newer ones with 5 x better
spec!

==================

Was about to post but sense some confusion about addins (not only you) -

When you saveas an addin (.IsAddin = True), the file is an Addin. Like an
xls/workbook except not visible and the save prompt does not appear if
changes are made.

Application.Addins, a collection of addins in the Addin Manager which may or
may not be installed. If installed a check appears in the addins list and it
will load when starting Excel. An additional macro security option allows
Medium yet no warning prompt. To be in the collection an addin has to be
"added", manually (tools addins) or with code.

Regards,
Peter T


"HSalim[MVP]" wrote in message
...
Peter,
Thanks for the suggestions - you and Tom have been a great help.
I will incorporate your suggestions.
re. On error Resume Next:
I'll check for the existence of the toolbar in a subroutine
(I feel that on error resume next should only be used in a tight little
routine that specifically checks for the anticipated error). I guess I
have
been burned by that statement in the past.

I like your suggestion that the user may have positioned the bar elsewhere
so I'll adopt that, but that brings up another problem:

When does the add-in close? when the user exits Excel, right? And it
loads
at excel start up, right? I am not sure how much memory each add-in
consumes and it might be a good idea to just start the add-in when it is
needed that one time a month. Your thoughts on that would be most
welcome.

Regards
Habib






"Peter T" <peter_t@discussions wrote in message
...
: Looks like you're there! To be ultra picky a couple more points -
:
: Set ws = Worksheets(1)
:
: I assume you are testing in an xls that becomes active when it opens,
unlike
: an xla. So you would need to qualify your Worksheet with the workbook.
: However if it's an xls, if user had moved the original Worksheets(1) it
will
: refer to wrong sheet. If an xls use sheet-name (though in the pasted
code
: "ws" is not used).
:
: Set ws = ThisWorkbook.Worksheets("Sheet1")
:
: and later in the code
: For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
: can be changed to
: For Each cel In ws.Range("Buttons")
:
:
: Set cBar = Application.CommandBars("LockBox")
: should be preceded with "On error Resume Next" in case the bar does not
: exist.
:
: This line from my previous suggestion
: ' If cBar.Controls.Count < 2 Then
: was intended if the bar exists but without at least the number of
expected
: controls (eg 2), then delete the bar and make a new one.
:
: I see you are deleting the bar if it exists and creating a new one each
: time. This is a normal thing to do particularly if the bar should have
been
: deleted on close. However if you are leaving it place on close,
originally
: you said user wants to keep it, then don't delete the bar. User may have
: positioned it to preference.
:
: I'm not sure if you are always going to create a new bar or only if
needs.
: If the latter might be an idea to move
: cBar.Visible = True
: to after the section starting "If cBar Is Nothing Then", ie ensure it's
: visible if you are not creating a new bar. However no harm to recreate
the
: controls each time even if the bar exists, eg
:
: If cBar Is Nothing Then
: Set cBar = Application.CommandBars.Add(Name:="LockBox")
: End If
: ' create the controls and set bar properties
:
: If your icons look OK I wouldn't worry about the additional code for
Picture
: & Mask, what you have will work in all versions. I think how "sharp"
they
: appear depends both on design and "type" of colours.
:
: Regards,
: Peter T
:
: PS When I posted the link to KeepITcools method I didn't see Tom's
slightly
: earlier post with same.
:
: "HSalim[MVP]" wrote in message
: ...
: Finally got it working. It is not quite waht I expected to see .
: I never did get the setIcon routine to work, not did the .Picture and
: .Mask
: methods work
:
: This is what I did.
: 1. added a new Picturename column to my template.
: 2. Pasted the button image as a picture in the worksheet
: 3. Asigned a name for each picture
:
: It appears that I can copy the image to memory and the paste the
button
: image on to the new button using .paste so here is the code snippet.
:
: Regards
: Habib
:
:
:
: '---------------------
: Sub Auto_Open()
: Dim cBar As CommandBar
: Dim i As Long, cel As Range
: Dim ob As Office.CommandBarButton
: Dim ws As Worksheet
:
: 'On Error GoTo 0
: 'On Error Resume Next
: Set ws = Worksheets(1)
:
: Set cBar = Application.CommandBars("LockBox")
: 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:="LockBox")
: cBar.Visible = True
:
: For Each cel In
: ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
: cBar.Controls.Add Type:=msoControlButton
: With cBar.Controls(cel.Value)
: .OnAction = cel.Offset(, 1).Value
: .Caption = cel.Offset(, 2).Value
: .TooltipText = cel.Offset(, 3).Value
: .Style = msoButtonIconAndCaption
: ws.Shapes(cel.Offset(, 4).Value).CopyPicture
xlScreen,
: xlBitmap
: .PasteFace
: .BeginGroup = True
: End With
: Next
: End If
:
: cBar.Enabled = True
: cBar.Position = msoBarTop
: End Sub
:
: '--------------------------------------
:
: "HSalim[MVP]" wrote in message
: ...
: : Peter,
: : It was not the file size that concerened me.
: : I just did not like the idea of having those image files outside -
now
I
: had
: : to be converned with handling errors if those files were deleted -
: troubles
: : with ensuring a consistent user experience etc...
: :
: : Looks like you have a bit of experience in this area, huh!
: : Thanks for the help.
: :
: : Regards
: : Habib
: :
: : :
:
:
:
:

Peter,
All noted with much gratitude.
And still at it - now is it is the refining phase, where I hope to iron out
the kinks I mught have introduced.
I am fairly new to Excel VBA so I don't always know the right / best way - I
just find something that works, so your pickiness is much appreciated.

Two things:
Where is that setting for macro security , and
Why is Range(x,y).Select bad?
Does that apply to ThisWorkbook/ThisWorksheet (addin )
and to Active Workbook as well?

In a recent problem, i found my code failing (or working incorrectly)
because I selected a range from bottom to top. The code has
selection.offset and because the activecell was on the bottom the offsets
did not work as I had wanted.
Range(x,y).select moved the activecell to the top left and then it was OK.

Regards
Habib


"Peter T" <peter_t@discussions wrote in message
...
: Still at it <g
:
: (I feel that on error resume next should only be used in a tight little
: routine that specifically checks for the anticipated error).
:
: I totally agree, but in this case we are anticipating an error.
Immediately
: after you could
: On Error Goto 0
: or
: On Error Goto errH
: 'code
: Exit Sub
: errH:
: Test why the error occurred, maybe "Resume" to another section. Not a lot
to
: go wrong but never can be 100% sure.
: End Sub
:
: When does the add-in close? , right?
:
: The Addin will always unload, when the user exits Excel, if user
uninstalls
: from the Addin Manager by unchecking (if in the Addin's collection) or if
: closed some other way - eg in the "Macro2" I posted earlier. Whatever way
it
: closes a close event will fire (assuming .EnableEvents has not been
disabled
: for any reason)
:
: And it loads at excel start up, right?
:
: Only if the Addin is installed in the Add manager and checked (Tools /
: Addins..) or if the addin is in the XLStart folder, or loaded by some
other
: addin in it's open event (I assume unlikely).
:
: I am not sure how much memory each add-in
: consumes and it might be a good idea to just start the add-in when it is
: needed that one time a month. Your thoughts on that would be most
: welcome.
:
: See my first post in this thread. It's my personal preference not to
: "install" infrequently used addins, but either to leave an invisible
custom
: toolbar or leave a single menu item on one of Excel's toolbars (ie don't
: delete it on unload). You say your user is familiar with how to activate
an
: invisible custom toolbar and only needs the addin once/mth. User can
choose
: either way - ie install as an addin or first time load from file (but
don't
: delete the toolbar in the close event).
:
: In the close event you could test if your xla is an installed addin, if so
: delete the toolbar, if not leave it in place.
:
: As to how much memory, that of course is relative to the size of your
addin,
: how much on sheets, how much code, state of compile and several other
: things. With modern systems I suppose typical addins are not much of an
: issue in this respect. Having said that I'm often amazed at how my old low
: spec system appears to run faster than some much newer ones with 5 x
better
: spec!
:
: ==================
:
: Was about to post but sense some confusion about addins (not only you) -
:
: When you saveas an addin (.IsAddin = True), the file is an Addin. Like an
: xls/workbook except not visible and the save prompt does not appear if
: changes are made.
:
: Application.Addins, a collection of addins in the Addin Manager which may
or
: may not be installed. If installed a check appears in the addins list and
it
: will load when starting Excel. An additional macro security option allows
: Medium yet no warning prompt. To be in the collection an addin has to be
: "added", manually (tools addins) or with code.
:
: Regards,
: Peter T
:
:
: "HSalim[MVP]" wrote in message
: ...
: Peter,
: Thanks for the suggestions - you and Tom have been a great help.
: I will incorporate your suggestions.
: re. On error Resume Next:
: I'll check for the existence of the toolbar in a subroutine
: (I feel that on error resume next should only be used in a tight little
: routine that specifically checks for the anticipated error). I guess I
: have
: been burned by that statement in the past.
:
: I like your suggestion that the user may have positioned the bar
elsewhere
: so I'll adopt that, but that brings up another problem:
:
: When does the add-in close? when the user exits Excel, right? And it
: loads
: at excel start up, right? I am not sure how much memory each add-in
: consumes and it might be a good idea to just start the add-in when it is
: needed that one time a month. Your thoughts on that would be most
: welcome.
:
: Regards
: Habib
:
:
:
:
:
:
: "Peter T" <peter_t@discussions wrote in message
: ...
: : Looks like you're there! To be ultra picky a couple more points -
: :
: : Set ws = Worksheets(1)
: :
: : I assume you are testing in an xls that becomes active when it opens,
: unlike
: : an xla. So you would need to qualify your Worksheet with the workbook.
: : However if it's an xls, if user had moved the original Worksheets(1)
it
: will
: : refer to wrong sheet. If an xls use sheet-name (though in the pasted
: code
: : "ws" is not used).
: :
: : Set ws = ThisWorkbook.Worksheets("Sheet1")
: :
: : and later in the code
: : For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
: : can be changed to
: : For Each cel In ws.Range("Buttons")
: :
: :
: : Set cBar = Application.CommandBars("LockBox")
: : should be preceded with "On error Resume Next" in case the bar does
not
: : exist.
: :
: : This line from my previous suggestion
: : ' If cBar.Controls.Count < 2 Then
: : was intended if the bar exists but without at least the number of
: expected
: : controls (eg 2), then delete the bar and make a new one.
: :
: : I see you are deleting the bar if it exists and creating a new one
each
: : time. This is a normal thing to do particularly if the bar should have
: been
: : deleted on close. However if you are leaving it place on close,
: originally
: : you said user wants to keep it, then don't delete the bar. User may
have
: : positioned it to preference.
: :
: : I'm not sure if you are always going to create a new bar or only if
: needs.
: : If the latter might be an idea to move
: : cBar.Visible = True
: : to after the section starting "If cBar Is Nothing Then", ie ensure
it's
: : visible if you are not creating a new bar. However no harm to recreate
: the
: : controls each time even if the bar exists, eg
: :
: : If cBar Is Nothing Then
: : Set cBar = Application.CommandBars.Add(Name:="LockBox")
: : End If
: : ' create the controls and set bar properties
: :
: : If your icons look OK I wouldn't worry about the additional code for
: Picture
: : & Mask, what you have will work in all versions. I think how "sharp"
: they
: : appear depends both on design and "type" of colours.
: :
: : Regards,
: : Peter T
: :
: : PS When I posted the link to KeepITcools method I didn't see Tom's
: slightly
: : earlier post with same.
: :
: : "HSalim[MVP]" wrote in message
: : ...
: : Finally got it working. It is not quite waht I expected to see .
: : I never did get the setIcon routine to work, not did the .Picture
and
: : .Mask
: : methods work
: :
: : This is what I did.
: : 1. added a new Picturename column to my template.
: : 2. Pasted the button image as a picture in the worksheet
: : 3. Asigned a name for each picture
: :
: : It appears that I can copy the image to memory and the paste the
: button
: : image on to the new button using .paste so here is the code
snippet.
: :
: : Regards
: : Habib
: :
: :
: :
: : '---------------------
: : Sub Auto_Open()
: : Dim cBar As CommandBar
: : Dim i As Long, cel As Range
: : Dim ob As Office.CommandBarButton
: : Dim ws As Worksheet
: :
: : 'On Error GoTo 0
: : 'On Error Resume Next
: : Set ws = Worksheets(1)
: :
: : Set cBar = Application.CommandBars("LockBox")
: : 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:="LockBox")
: : cBar.Visible = True
: :
: : For Each cel In
: : ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
: : cBar.Controls.Add Type:=msoControlButton
: : With cBar.Controls(cel.Value)
: : .OnAction = cel.Offset(, 1).Value
: : .Caption = cel.Offset(, 2).Value
: : .TooltipText = cel.Offset(, 3).Value
: : .Style = msoButtonIconAndCaption
: : ws.Shapes(cel.Offset(, 4).Value).CopyPicture
: xlScreen,
: : xlBitmap
: : .PasteFace
: : .BeginGroup = True
: : End With
: : Next
: : End If
: :
: : cBar.Enabled = True
: : cBar.Position = msoBarTop
: : End Sub
: :
: : '--------------------------------------
: :
: : "HSalim[MVP]" wrote in message
: : ...
: : : Peter,
: : : It was not the file size that concerened me.
: : : I just did not like the idea of having those image files outside -
: now
: I
: : had
: : : to be converned with handling errors if those files were deleted -
: : troubles
: : : with ensuring a consistent user experience etc...
: : :
: : : Looks like you have a bit of experience in this area, huh!
: : : Thanks for the help.
: : :
: : : Regards
: : : Habib
: : :
: : : :
: :
: :
: :
: :
:
:
:
:

Two things:
Where is that setting for macro security

Tools / Macros / Security, also look in the second Tab trusted Sources (not
xl97).

and
Why is Range(x,y).Select bad?
Does that apply to ThisWorkbook/ThisWorksheet (addin )
and to Active Workbook as well?

Except in a limited number of scenarios Select is at best unnecessary, slow,
causes flicker, moves the original selection (may need to re-select
original), might trigger unnecessary events and at worst can lead to errors
of varying degrees of seriousness.

If wanting to select in the non active sheet, first need to activate that
sheet, but if in another workbook first the workbook. Instead simply -

Dim rng as Range
Set rng = Workbooks("myBook.xls").Worksheets("Sheet1").Range ("A1")

If "myBook" is active not necessary to fully qualify as above as the
workbook ref is Implicit, similarly if "Sheet1" is also active. But if in
doubt fully qualify.

I expect if you look at your code you can change every instance of

Worksheets(1).activate
Range("A1:B2").Select
With Selection

'to
Set rng = Worksheets(1).Range("A1:B2")
With rng

or even
With Worksheets(1).Range("A1:B2")

Chances are you can also avoid disabling screen updating ??

Not quite sure what your offset problem is, not directly related to Select,
as "Selection" will refer to same as whatever you Set rng =

Strangely, it's possible to Activate a range in a hidden sheet of an addin
even though obviously the sheet is not active. I do this for a particular
reason but generally best avoided.

This is only a very brief intro. It's also way off topic so search this ng
for more, maybe start a new topic or if anything not clear in what I've
stated in this thread by all means contact me off-line (see below).

Regards,
Peter T
pmbthornton gmail com

<snip