attach custom toolbar to spreadsheet: how?

  • Thread starter Thread starter Bart Plessers \(artabel\)
  • Start date Start date
B

Bart Plessers \(artabel\)

Hello,

For a client I have made some macro's to speed up things in a spreadsheet.
I have created a custom toolbar, and attached the macro's to newly created
buttons.
Then I attached the toolbar to the spreadsheet.

However,
If I open the spreadsheet on another workstation for the first time, the
toolbar is opened, but no button is defined on it. I have also troubles to
get my macro's starting on the workbook.

So my question is:
-how can I create a custom toolbar that is attached to a certain spreadsheet
-the toolbar should be opened automatically when the spreadsheet is opened
-the toolbar should be deleted if the spreadsheet is closed (toolbar
commands are NOT available to other spreadsheets)

Is this possible? How?

thanx in advance,

bart plessers
 
Toolbars are application level. So there is no built in capability to have
them available only when a specific workbook is active. You would need to
write code using the workbook Activate and Deactivate events to show and
hide the toolbar. If you are going to do that, you might as well manage the
toolbar with code - creating it when the workbook is opened and deleting it
when the workbook is closed.

Here is a general list of Knowledge base articles and others related to
working with commandbars in code. I also included an article on attaching if
you want to know more about that.


-------------------------
Here is an article which talks about attaching toolbars:

http://www.microsoft.com/exceldev/articles/toolbatt.htm

Here is an article about creating commandbars with code:
http://msdn.microsoft.com/library/techart/ofcmdbar.htm

Here is another article or two you might find useful:
http://msdn.microsoft.com/library/backgrnd/html/msdn_addins97.htm
http://msdn.microsoft.com/library/officedev/odeopg/deovrcreatingexceladdin.htm
http://www.microsoft.com/exceldev/tips/addins.htm
These are about distributing applications

http://support.microsoft.com/?id=159619
XL97: Sample Macros for Customizing Menus and Submenus

http://support.microsoft.com/?id=213550
XL2000: Sample Macros for Customizing Menus and Submenus


http://support.microsoft.com/default.aspx?scid=kb;en-us;166755
File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R)
Excel 97
File Name: WE1183.EXE
File Size: 58041 bytes
File Date: 06/20/97
Keywords: kbfile kbappnote
Description: This Application Note can help you learn techniques for writing
Visual Basic(R) for Applications code to customize menus in Microsoft Excel
97. This Application Note contains code examples that you can use with the
following elements: menu bars, menus, menu items, submenus, and shortcut
menus.


http://support.microsoft.com/?id=288771
HOWTO: Create a Transparent Picture For Office CommandBar Buttons

http://support.microsoft.com/?id=160293
ACC97: How to Dim Menu Items or Disable Toolbar Buttons in Visual Basic for
Applications

http://support.microsoft.com/?id=198464
ACC2000: How to Dim Menu Items or Disable Toolbar Buttons in VBA

http://support.microsoft.com/?id=158550
XL97: Problems Disabling and Enabling Shortcut Menus

http://support.microsoft.com/?id=213561
XL2000: Problems Disabling and Enabling Shortcut Menus

http://support.microsoft.com/?id=213563
XL2000: Cannot Make Changes to Some Shortcut Menus

http://support.microsoft.com/?id=158434
XL97: Cannot Make Changes to Some Shortcut Menus

http://support.microsoft.com/?id=213209
XL2000: Sample Macros that Customize and Control Shortcut Menus

http://support.microsoft.com/?id=162878
XL97: Sample Macros That Customize and Control Shortcut Menus

http://support.microsoft.com/?id=213757
XL2000: How to Turn off Shortcut Menus

http://support.microsoft.com/?id=161440
XL97: How to Disable Shortcut Menus

http://support.microsoft.com/?id=170563
OFF97: How to Prevent Customization of Menus and Toolbars
Sub DisableToolbarMenu()
CommandBars("Toolbar List").Enabled = False
End Sub



http://support.microsoft.com/?id=161926
XL97: How to Place a Checkmark Next to a Menu Item

http://support.microsoft.com/?id=213735
XL2000: How to Place a Check Mark Next to a Custom Menu Item

http://support.microsoft.com/?id=211543
XL2000: Cannot Modify or Delete Custom Menus


http://support.microsoft.com/?id=213563
XL2000: Cannot Make Changes to Some Shortcut Menus

http://support.microsoft.com/?id=213700
XL2000: Custom Toolbar Is Added Above Existing Toolbars

http://support.microsoft.com/?id=167382
XL97: How to Restore a Built-In Menu

http://support.microsoft.com/?id=213708
XL2000: How to Hide and Restore a Built-In Menu

http://msdn.microsoft.com/library/officedev/office97/web/008.htm
MS Officer 97 Programmer's Guide
Chapter 8: Menus and Toolbars

=====================
http://support.microsoft.com/?id=241652
BUG: Changes Made to Excel CommandBars Through Automation Are Not Saved

This says it is fixed in SR1:

http://support.microsoft.com/?id=249066
XL2000: Changes Aren't Saved When Altering or Resetting Toolbars Using
Automation
 
Just one more question:
do you know how to position the toolbar?
I recreate the toolbar everytime the workbook is opened with Auto_Open and
delete the toolbar with Auto_Close when the workbook is closed (are there
better ways to achieve this? I want to have the macro's invisible to the
user...)
When the toolbar is create, it appears 'somewhere' on the screen. Can I
position it somewhere by use of macro? Can I have it docked to the main menu
bar?

thanx in advance

regards,
bart
 
The commandbar control has top and left properties. Here is an extract from
the help on the top property for a commandbar

CommandBar or CommandBarControl object: Returns or sets the distance (in
pixels) from the top edge of the specified command bar or command bar
control to the top edge of the screen. For docked command bars, this
property returns or sets the distance from the command bar to the top of the
docking area. Read/write Long for CommandBar, read-only Long for
CommandBarControl.



I don't think you can dock on the same row as the Worksheet Menu Bar, but
you can dock the commandbar on the next row.



--

Regards,

Tom Ogilvy
 
Back
Top