VBA custom command bar in add-in advice on code

[truskawa]

[FONT=&quot]Hi all,

I'm attempting for the first time to create a custom toolbar that will be distributed as an add-in with set of macros to my colleagues.
I need it to be fool-proof and I have some doubts about it.

1. when browsing through different tutorials on this I noticed that in many codes toolbar is added on auto_open and it is added every time excel is opened. Is there any reason for this? It slows down opening of excel a little bit and I was wondering if there is any particular reason why it should be reinstalled every time.

2. I noticed that add-in could work in two ways:
- adding it through add-ins menu or
- just opening .xla file.
In first case command bar is added every time on excel open (which also causes it to be placed in default position every time) in the second case command bar is added only once and keeps all functionalities of "ordinary" command bar so remembers if it was on or of and where it was placed.
My question is there any harm in installing an add-in the second way? Could it cause some problems or difficulties besides obvious things like not being visible on add in list?

Here is my current code: Please feel free to critique any "bad practices" in it since I'm self and internet taught and always like to improve my bad habits.

I would appreciate any advice on this.
Thanks a lot![/FONT]

Sub Autpen()
Dim cb As CommandBar
Dim ctrl As CommandBarControl
Dim name As CommandBarButton

On Error Resume Next
Application.CommandBars("Benefits Survey Toolbar").Delete
On Error GoTo 0
Set cb = Application.CommandBars.Add(name:="Benefits Survey Toolbar")

With cb
.Visible = True
.Position = msoBarTop
Set name = .Controls.Add(Type:=msoControlButton)
With name
.Style = msoButtonCaption
.Caption = "Benefits Toolbar"
End With
Set ctrl = .Controls.Add(Type:=msoControlButton)
With ctrl
.BeginGroup = True
.Style = msoButtonIconAndCaption
.Caption = "Unprotect"
.FaceId = 225
.OnAction = "Unprotect.Unprotect"
.TooltipText = "Unprotect workbook and worksheets"
End With
Set ctrl = .Controls.Add(Type:=msoControlButton)
With ctrl
.Style = msoButtonIconAndCaption
.Caption = "Query"
.FaceId = 535
.OnAction = "'" & ThisWorkbook.name & "'!Benefits_CopyMacro.Benefits_CopyMacro"
.TooltipText = "Add questions to clarification"
End With
Set ctrl = .Controls.Add(Type:=msoControlButton)
With ctrl
.Style = msoButtonIcon
.FaceId = 441
.OnAction = "'" & ThisWorkbook.name & "'!Check_categories.Check_categories"
.TooltipText = "Check employee cat. consistency"
End With
End With
End Sub

 

[truskawa]

Decided to bump it higher in more busy hour.
Any kind of help would be greatly appreciated.

 

[truskawa]

Bump.
Anyone?

If you need more info I would be glad to provide it.

 

[GTO]

Well... until you get better help, here are some strictly layman observations:

I have access to 2003 and 2000; if you are distributing to those with 2007+, I'm not sure toolbars can be used (excepting context), as there's a ribbon.

Disregarding above, IMO, adding the commandbar as a temporary toolbar and upon opening the add-in is better. This way, you don't end up leaving it when a user unloads the add-in.

I do not see anything wrong with your code. It appears you have a module and a procedure by the same name though (unprotect.unprotect). I would avoid this for clarity. For standard modules, 'mod' or 'bas' are often used as prefaces, which makes it easier to read the code.

Hope that's a tiny help,

Mark

 

[RoryA]

My question is there any harm in installing an add-in the second way?

You're not installing it if you do it that way, you're just opening it. As a result its Installed property will not be set and any other code that might check for addins will ignore it since it's not part of the addins collection.

Re the menus, it is better practice, IMO, to create the toolbar as a temporary toolbar on startup everytime, not least because it means that if the user accidentally deletes the toolbar, it will be available next time they start Excel. If you don't want to do that you can use the addininstall event to create the toolbar, but again that won't work if you simply open the xla rather than installing it.

 

[truskawa]

Thank you both! Finally some answers

@GTO
It will be distibuted to 2003 users only. **** ribbon...

Thanks for the tip on the modules names. I renamed them so it is easier to navigate between subs, but later, as I was writing toolbar I noticed that I need to use this name.name thing and I don't like it as well.

If as you both say it is better to install this toolbar through add-in menu. I decided I need to deal with issues I didn't like about it so:
- It was visible every time excel was opening
- It was always in new row of commandbars
- Was slowing down excel on open

I added short code at the beginning of the one quoted before. Thanks to this toolbar won't be reinstalled every time on open:

<font face=Courier New><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>cb_check = Application.CommandBars("Benefits Survey Toolbar").Index<br><SPAN style="color:#00007F">If</SPAN> cb_check <> 0 <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>

It seems that it works just fine. Do you see if this could cause any problem?

Once again thanks so much for help!

 

[RoryA]

That will be fine as long as you don't change the toolbar.

 

[truskawa]

Re the menus, it is better practice, IMO, to create the toolbar as a temporary toolbar on startup everytime, not least because it means that if the user accidentally deletes the toolbar, it will be available next time they start Excel.

Hi I wanted to get back to the subject. What did you mean as temporary toolbar? Is my toolbar temporary one? If not what are the advantages of it being temporary and how can I make it temporary?
I couldn't find anything on the subject :/

Also is it possible to write a sub that would be run when add in is removed?
I wanted the toolbar to be removed when add-in is deleted.

Thanks again!

 

[RoryA]

A temporary toolbar is removed whenever the applicaiton is closed. Yours is not temporary - to make it so, you would need a small change to the line that creates it:

Set cb = Application.CommandBars.Add(name:="Benefits Survey Toolbar", Temporary:=True)

The Workbook object has an AddinUninstall event that you can use to run code when it is uninstalled as an add-in.

 

[truskawa]

Thanks rorya. It works like a charm!