Printable View
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.
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.
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 use[vba]Create_Menu[/vba]and workbook_beforeclose use[vba]Delete_Menu[/vba]
[vba]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
[/vba]
Charlize
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!
Yes this will work with your add-in.....as Charlize says it is the preferred method of setting up your menu..
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?
Try using the AddinInstall and AddinUninstall events.
See if this article helpsQuote:
Originally Posted by markpilger
http://msdn.microsoft.com/archive/de...xltoolbatt.asp
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! TimQuote:
Originally Posted by xld