Solved: Add to Standard Toolbar

[wagnet]

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]

Hi wagnet,

Give the following a try, I've given you an example of everything you should need:[vba]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 Sub[/vba]Matt

[wagnet]

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]

ooops.

[tpoynton]

I think a good practice is to build and delete the menu each time excel is started. in ThisWorkbook, try adding the following:

[vba]
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
[/vba]
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]

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]

.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]

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]

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]

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:[vba] 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[/vba]Instead of adding a normal control, add another with Type:=10 (the menu, like we did to add it to the worksheet menu bar):[vba] 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 With[/vba]Matt

[gsouza]

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

[Zack Barresse]

That is a PopUp and not a button. Instead of using ID numbers, I like to use the text constant..

msoControlPopup
msoControlButton
etc

[mvidas]

That is a PopUp and not a button. Instead of using ID numbers, I like to use the text constant..

msoControlPopup
msoControlButton
etc

You never know if someone forgets to set their Office reference

[Zack Barresse]

Inconceivable!! LOL!

[gsouza]

Is it possible to place an Icon before the text?

[mvidas]

There sure is.. in the With block when adding a new control, specify the .FaceId property to set that icon:[vba] 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 With[/vba]You can get a listing of all the face id's using an addin like http://www.dicks-blog.com/archives/2...-viewer-addin/
Matt

[gsouza]

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]

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]

...
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...
[VBA]
Application.CommandBars(M).Controls(N).Enabled = False
[/VBA]

[wagnet]

How do I determine the Index number for the CommandBars and/or Controls?