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