Creating an add-in

[Peter T]

(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]]

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


: 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
:
:
: : > 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
: >
: >
: >
: >
: >
: >
: > : > : 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.
: > :
: > : : > : > 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
: > : >
: > : > '--------------------------------------
: > : >
: > : > : > : > : 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]

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>