Custom menu problems

[Andrew Fletcher]

Hi folks. Can anyone shed some light on the behaviour of custom menu
items? I have created a menu called 'Add plant'. THis sits to the
right of the 'Help' menu on the top line of the window. IT has drop
down menus to which I have added sub menu items to which I have
attached macros.

The menu is only specific to a particular work book and has no meaning
otherwise. I therefore only want to load this menu when I am using the
specific workbook. The other problem is if I use my workbook on
another PC the menu is not present.

Perhaps I am using the wrong feature for the job in hand bould would
appreciate any assistance or advice.

Thx

Andy F

 

[John]

Hi Andrew,

If it's just for a particular workbook, then place the code for the menu in
a workbook open event (double click the "ThisWorkbook" element in VBE window
and select "Workbook" from the left-hand drop down box). This should iniate
the menu as the workbook opens, plus the menu code will be embedded in that
workbook. You then just need to add a "remove menu" procedure to the Close
event (select this from the right-hand drop-down).

Something like this (where Add and Remove is your code):

Private Sub Workbook_Open()
Call AddCustomMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveCustomMenu
End Sub

Hope it helps.

John

 

[andrew fletcher]

Thx John,

Im not sure where the code exists for menu's. By menu's I just want to
clarify that I am talking about the custom menu's that can be added to
file|Edit|View|Insert etc etc... as opposed to toolbars. I appreciate
that toolbars can be turned on / off by accessing
view/toolbars/customise.

Im not too clued up on VB im afraid. I found 'this workbook' in the VB
editor which is a sub-menu of the VBA pproject. WHen clicked on it
brings up the properties.

Sorry for sounding a bit vacuous, suppose we all start smewhere. Could
you please try to expand on your explaination now that you have a feel
for my level of competence.

Thx - Andrew

 

[Guest]

Hi Andrew:

The following should help.

http://msdn.microsoft.com/library/en-us/dnoffpro01/html/customexcelmenus.asp?frame=true

Good Luck
TK

Sorry for sounding a bit vacuous, suppose we all start smewhere. Could
you please try to expand on your explaination now that you have a feel
for my level of competence.

Thx - Andrew

 

[Gord Dibben]

Andrew

Toolbar and menu customization/settings created with your method are saved in
your *.XLB file, which is on your computer and not accessible to others.

You could always export your *.xlb file to others, but this would destroy
their customizations.

You could also "attach" the Toolbar to the add-in via
Tools>Customization>Toolbars>Attach.

The best way to set up customizations is to create the buttons/toolbars/menus
through VBA so they appear when the workbook is opened and disappear when it
is closed.

Some instructions and code here.........

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno97ta/html/ofcmdbar.asp

Also John Walkenbach's downloadable MENUMAKR.XLS is a handy tool for
customizing menus.

Find it and sample code for creating "on the fly" toolbars.

http://www.j-walk.com/ss/excel/tips/commandbars.htm


Gord Dibben Excel MVP

 

[John]

Hi Andrew,

You need to double click the ThisWorkbook element to bring up the respective
code window on the right. This will be blank initially and the dropdown I'm
talking about will probably say something like "(General)". If you select
"Workbook", then it will automatically add an Workbook_Open Event procedure
(only the start and end). You can then add your own code or (before you
click the dropdown) paste in the code below.

I've used this the code below before. I hasten to add that this was someone
else's work that I modified and unfortunately I can't remember who's it was
to correctly atttibute it, although it appears to be pretty similar to the
msdn link that Gord sent you.

You'll need to change the "SubMenuItem..." and "MacroName.." part to your
own code, but hopefully this gives you the right idea.

Best regards

John

Private Sub Workbook_Open()
Call AddCustomMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveCustomMenu
End Sub

Private Sub RemoveCustomMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Add_Plant
Menu").Delete
End Sub

Private Sub AddCustomMenu()
Dim cbWSMenuBar As CommandBar
Dim muCustom As CommandBarControl
Dim iHelpIndex As Integer

Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar")

'Attempt to delete any old version of custom menu
'that might have been left hanging around by a crash.
On Error Resume Next
cbWSMenuBar.Controls("Add_Plant Menu").Delete
On Error GoTo 0

iHelpIndex = cbWSMenuBar.Controls("Help").Index
Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpIndex, temporary:=True)
With muCustom
.Caption = "&Add_Plant Menu"
With .Controls.Add(Type:=msoControlButton)
.Caption = "&SubMenuItem1"
.OnAction = "MacroName1"
.FaceId = 482
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "&SubMenuItem2"
.OnAction = "MacroName2"
.FaceId = 1084
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Sort Names &Ascending"
.BeginGroup = True
.OnAction = "SortList"
.FaceId = 1393
.Parameter = "Asc"
End With
End With
End Sub