PDA

View Full Version : Solved: Add to Standard Toolbar



wagnet
05-12-2006, 11:51 AM
I've built new toolbars, but I can't quite figure out how to add a new Control(?) to Excel's "Standard" toolbar.

On the Standard toolbar, I'd like to add a new menu, called "GSS", between the "Tools" and "Data" Controls.

Then, I want to add various sub-Controls under my new "GSS" menu.

Can someone, please, help me get started? How can I tell is the Standard toolbar is protected from changes? If it is protected, how do I unprotect it?

mvidas
05-12-2006, 12:04 PM
Hi wagnet,

Give the following a try, I've given you an example of everything you should need:Sub AddNewMenu()
Dim MainBar As Object, NewMenu As Object
On Error Resume Next
Set MainBar = Application.CommandBars("Worksheet Menu Bar")
Set NewMenu = MainBar.Controls.Add(type:=10, _
Before:=MainBar.Controls("Data").Index) '10=msoControlPopup

NewMenu.Caption = "&GSS"
With NewMenu.Controls.Add
.Caption = "&First menu option" 'the & makes it alt-key friendly
.OnAction = "'" & ThisWorkbook.Name & "'!ModuleName.MacroName"
.Tag = "First menu option"
End With
With NewMenu.Controls.Add
.Caption = "&Second menu option"
.OnAction = "'" & ThisWorkbook.Name & "'!ModuleName.MacroName"
.Tag = "Second menu option"
End With
With NewMenu.Controls.Add
.Caption = "&Third menu option"
.OnAction = "'" & ThisWorkbook.Name & "'!ModuleName.MacroName"
.Tag = "You get the idea"
End With
End Sub
Sub RemoveNewMenu()
Dim cBc As Object
On Error Resume Next
For Each cBc In Application.CommandBars("Worksheet Menu Bar").Controls
If cBc.Caption = "&GSS" Then
cBc.Delete
Exit For
End If
Next 'cBc
End SubMatt

wagnet
05-12-2006, 01:29 PM
Thanks, Matt. You make it look so easy.

How do I check to see if GSS already exists?

Basicly, I want to create the GSS Menu, then continue to add sub-menu items (Controls) under it, one at a time, as I add additonal macro/functionality.

I assume deleting the sub-menu (Controls) is the same as the RemoveNewMenu() macro --- just identify the Control name that I want to remove.

wagnet
05-12-2006, 01:29 PM
ooops.

tpoynton
05-13-2006, 08:24 AM
I think a good practice is to build and delete the menu each time excel is started. in ThisWorkbook, try adding the following:


Sub Workbook_AddinInstall()
AddNewMenu
end sub

Sub Workbook_AddinUninstall()
KillMenu

Dim xlaName As String
Dim i As Long

With ThisWorkbook
For i = 1 To AddIns.Count
xlaName = AddIns(i).Name

If xlaName = "YOURFILE.xla" Then 'be sure to put your filename here
AddIns(i).Installed = False
End If

Next i
End With
End Sub

Sub Workbook_BeforeClose() 'NOTE: I have a bunch of other stuff I do
RemoveNewMenu 'here as well; not 100% certain this is all you need
end sub

Sub Workbook_Open()
AddNewMenu
end sub

I usually find that other folks here have much more efficient ways of doing things than I do, so please dont consider this to be "the" solution. It's a weekend, so I thought I would throw my 2 cents in since i had a few moments.

This should address your question about checking if the menu already exists; since it is built each time Excel is started (or the add in is installed) and removed upon exit/uninstall, you shouldnt have to worry about multiple menus being built (I HOPE!)

regarding adding additional items as you add functionality, just add more items using the code mvidas provided above. if you want to remove something, comment out the "with...end with" part or delete it. You should not have to use the RemoveNewMenu routine to remove items from your new menu; that is used to remove the menu from the toolbar itself.

hope this makes sense!

wagnet
05-15-2006, 06:53 AM
Thanks for the response (and the sample code).

The "Standard" toolbar has several Controls (e.g. Format). Some of the sub-Control entries are grouped and a line divides the groupings (e.g. the line after the Format\Sheet Control.

How is that line created - or those groupings created?

Is it possible to change the font, text color, or background color or a specific sub-Control (is this referred to as a child?)?

I see how an Icon can associate the Control with the .FaceId command.

How do I assign an Alt-Key to a specific Control?

tpoynton
05-15-2006, 07:18 AM
.BeginGroup = True puts a line above the entry. Regarding your other questions:

changing font - I dont think so, but I am not sure

for the alt keys, you can use the "&" in the caption to define which letter will be underlined and work as a shortcut once the menu is selected/expanded, as "G" is in the example above.

Zack Barresse
05-15-2006, 03:38 PM
As a side note, if you are making use of a Class module with events for your command buttons, you'll want to use the Workbook_Open method as well as the Install/Uninstall.

gsouza
05-17-2006, 07:14 AM
Does anybody know how to make one of the menu options branch off to the right with another drop down and more options? For instance if you go to Data then down to Filter it has an arrow pointing to the right with another drop down with more options. Need help if you got it.

mvidas
05-17-2006, 07:48 AM
gsouza,

Usually, opening another question for this would be preferred. But since it is related, I can answer it here.

Using my example above, instead of doing: With NewMenu.Controls.Add
.Caption = "&First menu option" 'the & makes it alt-key friendly
.OnAction = "'" & ThisWorkbook.Name & "'!ModuleName.MacroName"
.Tag = "First menu option"
End WithInstead of adding a normal control, add another with Type:=10 (the menu, like we did to add it to the worksheet menu bar): With NewMenu.Controls.Add(type:=10, Before:=1)
.Caption = "new menu"
With .Controls.Add
.Caption = "&First menu option" 'the & makes it alt-key friendly
.OnAction = "'" & ThisWorkbook.Name & "'!ModuleName.MacroName"
.Tag = "First menu option"
End With
End WithMatt

gsouza
05-17-2006, 09:27 AM
I thought this was the place to reply since I was working on this entry. Thanks for the help, it works great. It impressed my boss :yes

Zack Barresse
05-17-2006, 10:12 AM
That is a PopUp and not a button. Instead of using ID numbers, I like to use the text constant..

msoControlPopup
msoControlButton
etc

mvidas
05-17-2006, 11:25 AM
That is a PopUp and not a button. Instead of using ID numbers, I like to use the text constant..

msoControlPopup
msoControlButton
etcYou never know if someone forgets to set their Office reference :)

Zack Barresse
05-17-2006, 01:12 PM
Inconceivable!! :bug: LOL!

gsouza
05-18-2006, 09:45 AM
Is it possible to place an Icon before the text?

mvidas
05-18-2006, 11:15 AM
There sure is.. in the With block when adding a new control, specify the .FaceId property to set that icon: With NewMenu.Controls.Add
.FaceId = 4
.Caption = "&First menu option" 'the & makes it alt-key friendly
.OnAction = "'" & ThisWorkbook.Name & "'!ModuleName.MacroName"
.Tag = "First menu option"
End With
With NewMenu.Controls.Add(type:=10, Before:=1)
.Caption = "new menu"
With .Controls.Add
.FaceId = 5
.Caption = "&First menu option" 'the & makes it alt-key friendly
.OnAction = "'" & ThisWorkbook.Name & "'!ModuleName.MacroName"
.Tag = "First menu option"
End With
End WithYou can get a listing of all the face id's using an addin like http://www.dicks-blog.com/archives/2004/11/23/faceid-viewer-addin/
Matt

gsouza
05-19-2006, 06:09 AM
Cool beans, Thank you for all your help. Anything off your head that I can do with this that is really neat? Any Suggestions that you might want to share?

wagnet
05-19-2006, 07:00 AM
Toolbars can be made "visible" (myBar.Visible = True), but can an individual Control or Item (.AddItem "GSS Tool") be set to .Visable = False?

I noticed in the Standard Excel toolbar, the Paste Control is low-lighted (e.g. greyed out) when nothing is on the clipboard. How is this accomplished?

johnske
05-19-2006, 07:51 AM
...
I noticed in the Standard Excel toolbar, the Paste Control is low-lighted (e.g. greyed out) when nothing is on the clipboard. How is this accomplished?Insert your numbers for M and N...

Application.CommandBars(M).Controls(N).Enabled = False

wagnet
05-19-2006, 08:13 AM
How do I determine the Index number for the CommandBars and/or Controls?

johnske
05-19-2006, 03:07 PM
use one of these...
Option Explicit
'
Sub CommandBarIDs()
'
'use either: File, Edit, View, Insert, Format, Tools, Data, Window, or Help
Dim MyControl As String
Dim Control As CommandBarControl
'
For Each Control In Application.CommandBars(1).Controls("help").Controls
MyControl = MyControl & Control.Caption & " " & Control.ID & vbNewLine
Next
'
MsgBox MyControl
'
End Sub
Or
Option Explicit
'
Sub ListAllCommandBarsAndIDs()
'
Dim MyControl As String, N As Long
Dim Control As CommandBarControl
'
Application.ScreenUpdating = False
Sheets.Add
'
For N = 1 To Application.CommandBars.Count
For Each Control In Application.CommandBars(N).Controls
MyControl = MyControl & Control.Caption & ": ID = " & Control.ID & vbLf
Next
Range("A" & N) = CommandBars(N).Name
Range("B" & N) = "Command Bar " & N
Range("C" & N) = MyControl
MyControl = Empty
Next
'
With Cells
.Columns.AutoFit
.Rows.AutoFit
.VerticalAlignment = xlTop
End With
'
Application.ScreenUpdating = True
'
End Sub

johnske
05-19-2006, 06:39 PM
How do I determine the Index number for the CommandBars and/or Controls?BTW. You can also do it this way...
'enter your own names
Application.CommandBars("Worksheet Menu Bar").Controls("File").Enabled = False :devil2:

Marcster
05-19-2006, 11:21 PM
Microsoft's knowledgebase article:
How to customize menus and menu bars in Excel
http://support.microsoft.com/?kbid=830502

Marcster.

gsouza
05-22-2006, 05:44 AM
I did it like this and it works

Application.CommandBars("Menu Bar").Controls(10).Controls(11).Enabled = False


That is 10 over from the left and 11 down the list.

wagnet
05-22-2006, 07:00 AM
It seems pretty straight-forward, but I'm getting an error:
Run-time error '91'
Object variable or With block variable not set

Here's the code:
Private Sub Workbook_AddinInstall()
CommandBars("Worksheet menu bar").Controls("&QuoteSummary V1").Enabled = True
TheEnd:
End Sub
Private Sub Workbook_AddinUninstall()
CommandBars("Worksheet menu bar").Controls("&QuoteSummary V1").Enabled = False
TheEnd:
End Sub

gsouza
05-22-2006, 07:40 AM
Application.CommandBars("Menu Bar").Controls(10).Controls(11).Enabled = False

you forgot Application.CommandBars and you can only use numbers in the contols(10) like above.

johnske
05-23-2006, 02:28 AM
Application.CommandBars("Menu Bar").Controls(10).Controls(11).Enabled = False

you forgot Application.CommandBarsTrue...




...and you can only use numbers in the contols(10) like above.Au contraire, this example does what it's supposed to :)
Application.CommandBars("Worksheet Menu Bar").Controls("File") _
.Controls("Send To").Controls("Mail Recipient").Enabled = False