Add File menu to custom toolbar

[michael.beckinsale]

Hi All,

I am trying to add the 'File" menu and "Window" menu of the Standard
Toolbar to a custom toolbar but failing miserably!

I think it should look something like:

Sub AddMenuTitles()
Application.CommandBars("MyCustomMenu").Controls.Add("File)
Application.CommandBars("MyCustomMenu").Controls.Add("Window")
End Sub

Can anybody help please

Regards

Michael

 

[Peter T]

Sub test()
AddMenuTitles True ' or false to delete
End Sub

Sub AddMenuTitles(bAdd As Boolean)
Dim cbr As CommandBar
Dim cbP As CommandBarPopup

On Error Resume Next
CommandBars("TestBar").Delete
On Error GoTo 0
If Not bAdd Then Exit Sub

' might want to include other arg's in .Add()
Set cbr = CommandBars.Add("TestBar", temporary:=True)

Set cbP = cbr.Controls.Add(ID:=30002) ' file
Set cbP = cbr.Controls.Add(ID:=30003) ' edit

cbr.Visible = True

End Sub

Regards,
Peter T

 

[michael.beckinsale]

Hi Peter,

Many thanks, nice piece of code.

A couple of questions if you dont mind:

1. Where did you get the ID's from?
2. How can l 'anchor' the custom toolbar at sat 'top left' bearing in
mind 3. below?
3. My intention is to delete the users existing Excel menu when
This.Workbook is active, present the user with my custom toolbar, and
then restore the users existing Excel menu when This.Workbook is
inactive. I think l know how to do this using the the
Workbook.Activate & Workbook.Deactivate events but l would br grateful
for any observations / pointers as menu manipulation is not something
l have coded much!

Regards

Michael

 

[Peter T]

(untested)
dim ctr as commandbarcontrol
for each ctr in CommandBars("Worksheet Menu Bar").controls
debug.? ctr.id, ctr.caption
next

The ID of Built-in controls is never 1, You can add copies of built-in
controls to your bar simply as shown.

Following toggles the main menu bar and docks the custom bar to top

Sub test()
AddMenuTitles True ' or false to delete
End Sub

Sub AddMenuTitles(bAdd As Boolean)
Dim cbr As CommandBar
Dim cbP As CommandBarPopup

CommandBars("Worksheet Menu Bar").Enabled = Not bAdd

On Error Resume Next
CommandBars("TestBar").Delete
On Error GoTo 0

If Not bAdd Then Exit Sub

Set cbr = CommandBars.Add("TestBar", Position:=msoBarTop,
temporary:=True)

Set cbP = cbr.Controls.Add(ID:=30002) ' file
Set cbP = cbr.Controls.Add(ID:=30003) ' edit

cbr.Visible = True

End Sub


Always a bit risky messing with user's settings, I tend not to like apps
that do that. But if needs dictate, call 'AddMenuTitles False' liberally in
all relevant thisworkbook events when your wb is no longer active, eg
beforeclose, deavtivate etc.

Regards,
Peter T

 

[michael.beckinsale]

Hi Peter,

Again thanks very much.

I agree with you re messing about with the users menu settings, hence
my lack of knowledge as l have infrequently coded the menu, however in
this instance the sponsor has requested a 'clean' look!

Regards

Michael