comandbars/toolbars

  • Thread starter Thread starter DENISE
  • Start date Start date
D

DENISE

Hello

I've been experimenting with

Application.commandbars(1).enabled = false

to disable toolbars

Question 1)
How many commandbars/toolbars are there (or how can I find
out) and how can you tell what number they are eg
Commandbar(1) is the menubar

Question 2)
Is there anyway of remembering which commandbars a user
has enabled, disable them when your workbook is opened and
enable them again when your workbook is closed.

Many thanks in advance
 
In line
DENISE said:
Hello

I've been experimenting with

Application.commandbars(1).enabled = false

to disable toolbars

Question 1)
How many commandbars/toolbars are there (or how can I find
out) and how can you tell what number they are eg
Commandbar(1) is the menubar
Click to expand...

CommandBars.count

for each cbar in CommandBars
cells(i,1).Value = cbar.Name
cells(i,1).Value = cbar.Enabled
cells(i,1).Value = cbar.Visible
Next

except for the Worksheet Menu Bar and the Chart Menu Bar, you usually make a
commandbar not visible rather than use the enable property.
Question 2)
Is there anyway of remembering which commandbars a user
has enabled, disable them when your workbook is opened and
enable them again when your workbook is closed.
Click to expand...

Loop through them and check their status. Record what it is. Use your
recorded information to restore them.
Many thanks in advance
Click to expand...

some additional Information:

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
=====================

from Stratos:
http://msdn.microsoft.com/library/techart/ofcmdbar.htm
http://msdn.microsoft.com/library/techart/msdn_replace.htm
http://msdn.microsoft.com/library/officedev/odeopg/deovrworkingwithcommandbarcontrols.htm
http://msdn.microsoft.com/library/officedev/office97/web/008.htm


Posted by Alyda Gilmore
This site has an interesting article regarding customization of both the
"Cell" and "Ply" commandbar menus:
http://www.officevba.com/features/1997/10/vba199710dr_f/vba199710dr_f.asp
Download the sample code:
http://www.officevba.com/features/1997/10/vba199710dr_f/vba199710dr_f.zip





http://www.officevba.com/features/1997/10/vba199710dr_f/vba199710dr_d.asp

This takes you to a page with a brief narrative and the "live words"

"Download the sample code." which has the URL below.

http://www.officevba.com/features/1997/10/vba199710dr_f/vba199710dr_f.zip


http://support.microsoft.com/?id=159466
Q159466 - XL97: List of ID Numbers for Built-In Command Bar Controls

http://support.microsoft.com/?id=213552
Q213552 - XL2000: List of ID Numbers for Built-In CommandBar Controls

http://support.microsoft.com/?id=162814
Q162814 - XL97: Sample Macros to Return ID for a CommandBar Control

http://support.microsoft.com/?id=213211
Q213211 - XL2000: Sample Macros to Return ID for a CommandBar Control
 
Hi Denise,

Here is some code that will do what Tom was describing:
Dim x() As Integer
Dim y, Counter As Integer
Dim CB1 As CommandBar

'initialize the counter
Counter = 0
'Set the array size of x to the number of commandbars
ReDim x(Application.CommandBars.Count)

'Loop through all command bars
For Each CB1 In Application.CommandBars
'if one is visible, then disable it and store
'it's index in an array of x
If CB1.Visible Then
CB1.Enabled = False
x(Counter) = CB1.Index
Counter = Counter + 1
End If
Next CB1

'Loop through the x array holding the
'previously enabled commandbars
For y = 0 To UBound(x)
'If no more entries, then exit the loop
If x(y) = Empty Then Exit For
'Set them back to enabled
Application.CommandBars(x(y)).Enabled = True
Next y

To disbale the commandbars upon opening your workbook, you'll have to put the close code in the workbook_open sub and to re-enable them, put that code in the workbook_close sub. Don't forget you'll have to dim x as a global variable, though.

-Dan
 
Back
Top