Add-In Toolbar Control - Removing The Toolbar Question

[Dreiding]

I'm working on an Excel Add-In that has a custom toolbar. When it's not an
Add-In the toolbar is defined when I open the file (part of workbook_open)
and is removed (before workbook close). I save it as an Add-In. When I open
excel and enabler my Add-In the toobar is displayed. This is just what I
want and expected. My question to you is: What am I doing wrong such that
when I uncheck the Add-in the toolbar remains until I exit and reenter Excel.


What do I need to do to to have the Add-In toolbar removed when unselecting
it from the Add-In dialog?

TIA,
- Pat

 

[Jim Cone]

Pat,
Put your code to create the toolbar in the... Private Sub Workbook_AddinInstall() sub.
Put the code to delete the toolbar in the... Private Sub Workbook_AddinUninstall() sub.
Both are event subs in the ThisWorkbook module.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Dreiding"
wrote in message
I'm working on an Excel Add-In that has a custom toolbar. When it's not an
Add-In the toolbar is defined when I open the file (part of workbook_open)
and is removed (before workbook close). I save it as an Add-In. When I open
excel and enabler my Add-In the toobar is displayed. This is just what I
want and expected. My question to you is: What am I doing wrong such that
when I uncheck the Add-in the toolbar remains until I exit and reenter Excel.

What do I need to do to to have the Add-In toolbar removed when unselecting
it from the Add-In dialog?
TIA,
- Pat

 

[Bill Renaud]

You should be using the Workbook_AddinInstall and Workbook_AddinUninstall
events handlers in the ThisWorkbook code module. Depending on how
customized your toolbar is, you may not need to actually create the toolbar
in VBA code. Simply create your toolbar at design time and attach it to the
workbook. (Make sure you delete and re-attach it whenever you make any
editing changes to the toolbar!) Your toolbar will be displayed
automatically when you install or open the workbook.

Use ThisWorkbook.IsAddin to determine which case fits the situation. The
only code you should have to write is to delete the toolbar at the proper
time. In this situation (a workbook now, but add-in in the future), I
sometimes add a button with an "X" at the right end of the toolbar so that
I can delete the toolbar manually when the workbook is still in the regular
workbook stage. Then the Workbook_BeforeClose event would not be used.

Try the following code:

'----------------------------------------------------------------------
Private Sub Workbook_AddinUninstall()
On Error Resume Next

Application.CommandBars("My Toolbar").Delete
End Sub

'----------------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next

If ThisWorkbook.IsAddin _
Then
'Do nothing now. Wait until Addin is Uninstalled.
Else
'Workbook is a normal workbook. Delete toolbar now.
Application.CommandBars("My Toolbar").Delete
End If
End Sub

 

[Dreiding]

Bill,

After a few iterations I was successful following your advice. The toolbar
is not complex so I attached it to the workbook.
Here's a follow-up question.
When executed, my Add-In macro looks for a specific worksheet in the
activeworkbook and displays it (test case). The first time when I execute
the macro after enabling the Add-In, the macro uses the Add-In (*.xla) file
as the activeworkbook. Every macro execution afterwards works correctly on
the 'real' activeworkbook. Why does the Add-In think it's the activeworkbook
the first time through? What am I doing wrong? Do I need to add code to
'Workbook_AddinInstall' ?

Thanks
- Pat

 

[Bill Renaud]

When you say that your "... Add-In macro looks for a specific worksheet in
the active workbook and displays it", where is this code exactly? Are you
trying to display a worksheet that is in the add-in for some reason?

In general, the Workbook_AddinInstall event handler should only be doing
things that initialize your add-in (like fetching values from the VBA
Settings in the Registry, hiding other toolbars that aren't needed yet,
etc.). There isn't much else that the add-in should be doing, especially
doing something in another regular workbook, since any regular workbook
really is not the active workbook while the add-in is busy installing.

Remember that an add-in is loaded while Excel starts up, and then remains
installed and available to all workbooks during the entire session, until
you quit Excel. So technically, there aren't supposed to be any other
workbooks open yet. Your add-in is effectively an extension to the
functionality of Excel (sort of like installing a new version of Excel with
a new feature!).

Your toolbar buttons should be used to operate on the active workbook, and
should be calling regular subroutines in a standard code module in your
add-in. Activeworkbook should work correctly, as expected.

Post your complete code for both Workbook_AddinInstall and
Workbook_AddinUninstall event handlers. You probably have included a lot of
other stuff that should not be in these 2 event handlers.

 

[Bill Renaud]

Another helpful tip when putting code in a workbook that will normally not
be the active workbook:

After attaching the toolbar to the macro workbook, I normally hide the
workbook, then go into the VBA editor and save the macro workbook from
there, using the Visual Basic File|Save command. In this way, the macro
workbook will not be the active workbook and will not be displayed and
possibly confuse the user. When a macro workbook is saved as an add-in, the
worksheets are not displayed anyway. The user should be putting their data
(which the add-in will work on) in a separate workbook, which would be the
active workbook, since it is visible.

(Of course, if you make any changes to the macro workbook toolbar, you will
have to unhide the macro workbook in order to delete and then re-attach the
toolbar. Just remember to hide the macro workbook and save from inside the
VBA editor.)

 

[Dreiding]

Bill,

Thanks for all your inputs. I added the code "msgbox activeworkbook.name"
to the
Workbook_AddinInstall event handle and the macro executed by one of the
toolbar button. The Workbook_AddinInstall showed the correct workbook, the
macro showed the macro.xla file when executed the first time. Also, I could
not select a cell or do anything to my activeworkbook until I triggered the
macro. Very strange.

However.... I did find a fix. Turns out the Excel did not like module
level Private Constants. I changed then to variables, added then to an
initialization sub and added a call to the initialization sub in
Workbook_AddinInstall. It all works cleanly now.

Is there someplace I can find the rules of thing not to do in Add-In?

Thanks
- Pat

 

[Bill Renaud]

Pat wrote:
<<Turns out the Excel did not like module level Private Constants. I
changed then to variables, added then to an initialization sub and added a
call to the initialization sub in Workbook_AddinInstall.>>

Private Constants should not be a problem at all. "Private" just means that
they can only be "seen" (used) by code in the same module. As long as your
initialization subroutine is in the ThisWorkbook, it should work fine. If
you placed your initialization subroutine in a standard code module, then
yes, the Constant would have to be declared there.

Pat wrote:
<<Is there someplace I can find the rules of thing not to do in Add-In?>>

Now you are getting into VBA programming! I can only suggest a few
starters:

In Excel Help, scroll down the Contents tab until you come to "Programming
Information", open up that chapter and scroll down to "Visual Basic
Conceptual Topics". Read the following topics:

Declaring Constants
Understanding Scope and Visibility
Using Constants

Also, you might check Microsoft's web site and search for "Building an
Excel add-in". I found the following topic (among many others):

"Creating an Excel Add-in"
http://msdn2.microsoft.com/en-us/library/aa140936(office.10).aspx

Hopefully, this will give you a start. Admittedly, all of the information
that you need to know is scattered all over the place (both the Microsoft
web site and Excel Help).

You will probably have to break down and buy a couple of books (even books
for older versions of Excel at a half-price store are a good deal, since
95% of the material is still valid for whatever version you are using; try
to get one that still has the CD-ROM in the back!).

 

[Bill Renaud]

For additional info about add-ins, you might check the following page on
Chip Pearson's web site:

Automation Add Ins As Functions Libraries For Excel And VBA
http://www.cpearson.com/excel/automationaddins.aspx

 

[Dreiding]

Bill,

I have a test for you to run in Excel 2003.
You will need to create a new workbook, attach a toolbar & button, assign a
macro, save it as an Add-In. I've numbered the steps.

1. In the "ThisWorkbook" even handler, insert:
'--------------------------------------------------------------------------------
Option Explicit

Private Sub Workbook_AddinInstall()
MsgBox "Workbook_AddinInstall - " & ActiveWorkbook.Name
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not ThisWorkbook.IsAddin Then Application.CommandBars("My Test").Delete
End Sub
'---------------------------------------------------------------------------------------------

2. In a new Module, insert:
'--------------------------------------------------------------------------------------------
Option Explicit
Option Private Module

Sub MyTest()
MsgBox "My Test - " & ActiveWorkbook.Name
End Sub
'------------------------------------------------------------------------------------------

3. Add and Attach a "My Test" Toobar with a button that execute the 'MyTest'
macro

4. Save the file as an Add-in (Give it a good name 'My Test')

5. Exit Excel, Open Excel with the default workbook. Add-In the Toolbar and
Click the button.

I get different workbook names when their first executed fro each of the
ActiveWorkbook.name executed.

Does this happen for you?

Thanks
- Pat

 

[Bill Renaud]

Pat wrote:
<<I get different workbook names when they're first executed for each of
the ActiveWorkbook.Name executed.>>

Yes, I get this same result in Excel 2000. So the add-in considers itself
the active workbook during the install process (or at least while the
Workbook_AddinInstall event is running).

Also, I had to delete (comment out) the statement:

Option Private Module

.... in the standard code module before I could connect the toolbar button
to the macro using the Customize dialog box. (Normally, this statement is
not really needed, unless you have procedures by the same name in another
workbook, and don't want VBA to get confused.)