SOLVED: Add-in .xla w/ toolbar not finding assigned macro

[MrRhodes2004]

Hey Group,



I?ve created an add-in that has many functions and is used by several people around the office. The file.xla is stored on the network where everyone has access. The .xla was created with a worksheet that was saved as an .xla.

In addition to the code, I have also created a toolbar that runs some of the macros. The toolbar was created using the View > Toolbars > Customize dialog box. The toolbar was then attached to the worksheet prior to saving as an .xla file. This process has worked well when I have not had to move the .xla file.

The .xla file is now desired in another office but when they install it and click on the toolbar buttons an error occurs stating, ?The macro ?I:\....xxx.xla?!macroname? could not be found.?

The toolbar dialog referenced the exact location of the .xla file and so it does not transport well. To test it, I copied it from the network I:\ drive to my desktop. From excel, I deleted the previous add-in association then created a new association to the .xla file on my desktop. When I did this, I got the error.



Is there a way to convert the existing toolbar into VBA code so that when the code is loaded, it will know where the .xla file is located?



Or, is there a way to assign the macro of the buttons in the toolbar to the .xla no matter where the .xla is loaded from?



Finally, is there a better way to do any or all of the above?



Thanks.

[tpoynton]

I would think that creating the menu programmatically (as opposed to creating it via the custom toolbar) would begin to resolve the issue...is it possible to post the file?

this KB entry creates a menu as part of an xla - http://vbaexpress.com/kb/getarticle.php?kb_id=807 - i would suggest posting the code from ThisWorkbook in your file to see what's going on there.

also, when you copied the file from the network to your desktop, did you get an error saying something like "delete previous entry'? I have seen the error you describe in my own work and vaguely recall that it had something to do with either that (delete previous entry), or not "killing" the menu when uninstalling / installing the addin. the menu you see may not be associated with the addin code for some reason, which could cause the error you describe

[Bob Phillips]

Here is some code that makes a stab at extracting the menu create commands from your toolbar.

It writes to the Immediate window, and whilst not perfect, should get you started. If you have any problems with it, hopefully someone can help you tomorrow.



Sub testcb()
CBDetails Application.CommandBars("Custom Toolbar")
End Sub
Function CBDetails(octl As Object) As Boolean
Dim bResult As Boolean
Dim ctl As CommandBarControl
If TypeOf octl Is CommandBar Then
Debug.Print " On Error Resum Next"
Debug.Print " Application.Commandbars(""" & octl.Name & """).Delete"
Debug.Print " With Application.commandbars.Add(Name:=""" & octl.Name & """,Temporary:=True)"
ElseIf TypeOf octl Is CommandBarPopup Then
Debug.Print " With .Controls.Add(Type:=msControlPopup)"
Debug.Print " BeginGroup = " & octl.BeginGroup
Debug.Print " .Caption = """ & octl.Caption & """"
Debug.Print " .OnAction = """ & octl.OnAction & """"
ElseIf TypeOf octl Is CommandBarButton Then
If octl.ID <> 1 Then
Debug.Print " With .Controls.Add(Type:=msControlButton, ID:=" & octl.ID & ")"
Else
Debug.Print " With .Controls.Add(Type:=msControlButton)"
End If
Debug.Print " BeginGroup = " & octl.BeginGroup
Debug.Print " .Caption = """ & octl.Caption & """"
Debug.Print " .OnAction = """ & octl.OnAction & """"
Debug.Print " End With"
End If

On Error GoTo CBDetails_Exit
For Each ctl In octl.Controls
CBDetails ctl
Next ctl

If TypeOf octl Is CommandBar Then
Debug.Print " End With"
ElseIf TypeOf octl Is CommandBarPopup Then
Debug.Print " End With"
End If

CBDetails_Exit:
On Error GoTo 0
End Function

[MrRhodes2004]

Thanx xld!
That code is pretty good. I only had to correct a couple of items but it did exactly what I needed. With almost 70 buttons it would have been very difficult to write the code for them all. Thanks again.

Items to adjust in your code to make it work with 2003: I had to adjust the "ms..." to "mso..." and had to add a period in front of the ".BeginGroup".

I had so many buttons that I had to step through the code to be able to get it all out of the immediate window!

THANKS AGAIN!

[XLGibbs]

xld....great bit of code there...

Question/POI along these lines....is it generally accepted that creating the menu in code is a better option than using toolbar customize? particularly for a shared xla?

Further....are there any pros/cons that specifically relate to the shared location of the add in?

I can think that if one add-in location accessible by all makes updating the add in easier, but I am unaware of the pitfalls.

I ask because I am laying down the finishing touches on a loaded "easy button" add-in for folks at work and am debating about where to locate it (either in an accessible network drive, or to each user and installed in the Add-ins folder...)

Currently I create the toolbar via code, and I am a big fan of that process, so i don't anticipate changing.

Don't mean to hijack the thread here....but perhaps the answers would provide others guidance as well in their development..(to avoid the same issue the OP is going through)

[MrRhodes2004]

My mentor and I been running code on the network drive for a long time and it seems to be the best place for it. I would suggest that once you place it there, make it read-only so that users won't "lock" it preventing you from making updates. Secondly, when the user does install it, tell them not to copy it to their local drive, otherwise, updates will be useless. I just maintain mine on the network and replace it with a newer version. This way the user always has un updted version each time they restart excel.

As for the the toolbar, if the .xla is going to be shared, it is very important to write the code instead of using the customize function! It causes a lot of problems if you don't specifically control the toolbar information.

What is good code to determine where the .xla file and been placed and initialized from? Since I share mine with other offices, I cannot control the file folder structure and I would like one of my buttons to open a workbook that is placed in the same unknown folder.

[Zack Barresse]

XLGibbs, I'm working on an article for that right now actually. Hopefully it'll be ready soon. :)

[XLGibbs]

{Snip}
As for the the toolbar, if the .xla is going to be shared, it is very important to write the code instead of using the customize function! It causes a lot of problems if you don't specifically control the toolbar information.


Oh yeah, I rarely if ever use or even recommend the customize function. All my toolbars are programmatically created...much safer that way.

I was just concerned about file opening time...and I thought as well that a read only version on the network would be best, to resolve "update" issues.

Thanks for the other information as well!

Zack, looking forward to the article!

Pete

[Bob Phillips]

Question/POI along these lines....is it generally accepted that creating the menu in code is a better option than using toolbar customize? particularly for a shared xla?

IMO it is always best to build the menu with code. Personally, I have a table driven procedure for building it, so that I can add each item to a worksheet table and visualise my menu.


Further....are there any pros/cons that specifically relate to the shared location of the add in?

I can think that if one add-in location accessible by all makes updating the add in easier, but I am unaware of the pitfalls.

The general wisdom is that the best place to store the xla is on a network and access it from there. Personally, I prefer to source it from a network, but install it on each desktop.

Ken started an interesting thread on this subject here (http://www.vbaexpress.com/forum/showthread.php?t=5179)

[Bob Phillips]

I only had to correct a couple of items but it did exactly what I needed. With almost 70 buttons it would have been very difficult to write the code for them all.

Items to adjust in your code to make it work with 2003: I had to adjust the "ms..." to "mso..." and had to add a period in front of the ".BeginGroup".

Thanks, I might make a utility out of that.


I had so many buttons that I had to step through the code to be able to get it all out of the immediate window!

You could have done it all in one hit. Even if you can't see everything in the immediate window, you can access it, just do a Ctrl-A.