Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Toolbar Control - Removing The Toolbar Question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Toolbar Control - Removing The Toolbar Question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Toolbar Control - Removing The Toolbar Question
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 -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Toolbar Control - Removing The Toolbar Question
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Toolbar Control - Removing The Toolbar Question
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. -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Toolbar Control - Removing The Toolbar Question
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.) -- Regards, Bill Renaud |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Toolbar Control - Removing The Toolbar Question
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" wrote: 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.) -- Regards, Bill Renaud |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Toolbar Control - Removing The Toolbar Question
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/lib...ffice.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!). -- Regards, Bill Renaud |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Toolbar Control - Removing The Toolbar Question
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 -- Regards, Bill Renaud |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Toolbar Control - Removing The Toolbar Question
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Toolbar Control - Removing The Toolbar Question
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.) -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form Toolbar verses Control Toolbar | Excel Discussion (Misc queries) | |||
Having problem removing toolbar envelope. | Excel Discussion (Misc queries) | |||
Removing custom toolbar from Excel 2003 | Excel Discussion (Misc queries) | |||
removing acontrol from the toolbar | Excel Programming | |||
removing a toolbar with code | Excel Programming |