PDA

View Full Version : Custom Menu Item not following document



markpilger
03-02-2007, 01:13 PM
I have created a new toolbar, added a custom menu item to it, and attached the toolbar to the workbook. Whenever I open the workbook I have the toolbar and menu items I created show up. When someone else opens the workbook the toolbar displays, but the custom menu items do not display. What do I need to do to get these to display? Thanks in advance for the help.

TrippyTom
03-02-2007, 02:08 PM
check that person's macro security settings. It's it's set to medium, they have to enable it when opening the file. If it's set to High, macros won't run at all.

Charlize
03-03-2007, 01:31 PM
Better built your toolbar on opening of the workbook. That way it won't matter because the toolbar is always built on opening (on closing you remove the toolbar). Workbook_open event useCreate_Menuand workbook_beforeclose useDelete_Menu
Sub Create_Menu()
Dim MyBar As CommandBar
Dim MyButton As CommandBarButton
Delete_Menu
'name of your floating bar
Set MyBar = CommandBars.Add(Name:="Two Buttons", _
Position:=msoBarFloating, temporary:=True)
With MyBar
.Top = 50
.Left = 650
'the two buttons that you want
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Back to main"
.Style = msoButtonCaption
.BeginGroup = False
'here you refer to the code to execute when you select this option
'put code to execute in module (not a private one)
.OnAction = "Macro_to_go_to_main"
End With
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Back to top"
.Style = msoButtonCaption
.BeginGroup = False
.OnAction = "Macro_to_go_to_top"
End With
.Width = 150
.Visible = True
End With
End Sub
Sub Delete_Menu()
On Error Resume Next
'remove menu when active before activating again.
CommandBars("Two Buttons").Delete
On Error GoTo 0
End Sub

Charlize

SherryO
05-02-2007, 08:13 AM
Would this work with an add-in? I'm trying to put a custom menu into an add-in. Would this be the best way? Thanks!

lucas
05-02-2007, 08:27 AM
Yes this will work with your add-in.....as Charlize says it is the preferred method of setting up your menu..

tpoynton
05-02-2007, 06:15 PM
not to hijack this thread (again?), but it is closely related!

I build and delete a toolbar as indicated above (using the open and beforeclose events) with an add-in, and it does work very well! The only time I run into a problem is when I close excel, then decide that I didnt really want to close it and choose 'cancel' from the excel dialog asking if I want to save changes to the workbook. The toolbar is deleted, but the add-in is still loaded (as it should be).

Any way to add something to the beforeclose event to rebuild the toolbar if the cancel button is clicked?

Bob Phillips
05-02-2007, 07:42 PM
Try using the AddinInstall and AddinUninstall events.

Bob Phillips
05-02-2007, 08:36 PM
I have created a new toolbar, added a custom menu item to it, and attached the toolbar to the workbook. Whenever I open the workbook I have the toolbar and menu items I created show up. When someone else opens the workbook the toolbar displays, but the custom menu items do not display. What do I need to do to get these to display? Thanks in advance for the help. See if this article helps

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarexcel9/html/xltoolbatt.asp

tpoynton
05-03-2007, 05:05 AM
Try using the AddinInstall and AddinUninstall events.

Thanks Bob! I initially had used those events; problem was that users would delete the xla, and not uninstall, the correct way, leaving the menu (most users know little about Excel). Using the beforeclose event deals with that effectively. It's not that big of a problem, but reading this thread piqued my interest! Tim