Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Question on custom commandbars
Hi All,
I have a question for the Excel experts. How do you get custom commandbars to only open with a specific workbook, then close when the workbook is closed? I already use an auto_open macro for certain tasks. Would I insert commanbar = true statements into the macro? If I did this, how would I get the commandbar(s) to automatically close when I exit the workbook? Thanks for any help. Mark |
#2
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Question on custom commandbars
Mark,
You're looking for the Temporary parameter, which prevents Excel being stuck with it. You can have the Auto_Close procedure destroy the toolbar too. Const cCommandBar = "MyCommandBar" Dim bar As CommandBar For Each bar In Application.CommandBars If bar.Name = cCommandBar Then bar.Delete Next I have an example for Commandbars he http://www.vangelder.co.nz/excel/index.html Rob "Mark Reynolds" wrote in message ... Hi All, I have a question for the Excel experts. How do you get custom commandbars to only open with a specific workbook, then close when the workbook is closed? I already use an auto_open macro for certain tasks. Would I insert commanbar = true statements into the macro? If I did this, how would I get the commandbar(s) to automatically close when I exit the workbook? Thanks for any help. Mark |
#3
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Question on custom commandbars
This file might be a help:
http://www.bygsoftware.com/examples/...tiveWbDemo.zip It's in the "Menu Routines" section on page: http://www.bygsoftware.com/examples/examples.htm It contains VBA code that will activate a menu only when the workbook it is in is active. The code is open and commented. -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Mark Reynolds" wrote in message ... Hi All, I have a question for the Excel experts. How do you get custom commandbars to only open with a specific workbook, then close when the workbook is closed? I already use an auto_open macro for certain tasks. Would I insert commanbar = true statements into the macro? If I did this, how would I get the commandbar(s) to automatically close when I exit the workbook? Thanks for any help. Mark |
#4
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Question on custom commandbars
Hi Mark -
Use a macro to build your commandbar. It's fast, pretty reliable, and easier to change that if you attach it. Put these into the ThisWorkbook code module of the workbook. The Workbook_Open and _BeforeClose event procedures build and destroy the commandbar, the _Activate and _Deactivate procedures show and hide it. Option Explicit Private Sub Workbook_Open() Create_Menu End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Delete_Menu End Sub Private Sub Workbook_Activate() On Error Resume Next Application.CommandBars(MENU_NAME).Visible = True On Error GoTo 0 End Sub Private Sub Workbook_Deactivate() On Error Resume Next Application.CommandBars(MENU_NAME).Visible = False On Error GoTo 0 End Sub Here's some sample code to build a commandbar. Put it into a regular code module in the same workbook. Option Explicit Public Const MENU_NAME As String = "My Menu" Sub Create_Menu() Dim MyBar As CommandBar Dim MyPopup As CommandBarPopup Dim MyButton As CommandBarButton Delete_Menu Set MyBar = CommandBars.Add(Name:=MENU_NAME, _ Position:=msoBarFloating, temporary:=True) With MyBar .Top = 125 .Left = 850 Set MyPopup = .Controls.Add(Type:=msoControlPopup) With MyPopup .Caption = "Popup 1" .BeginGroup = True Set MyButton = .Controls.Add(Type:=msoControlButton) With MyButton .Caption = "Button 1a" .Style = msoButtonCaption ''' msoButtonAutomatic, msoButtonIcon, msoButtonCaption, ''' or msoButtonIconandCaption .BeginGroup = True .OnAction = ThisWorkbook.Name & "!Macro1a" End With Set MyButton = .Controls.Add(Type:=msoControlButton) With MyButton .Caption = "Button 1b" .Style = msoButtonCaption .BeginGroup = False .OnAction = ThisWorkbook.Name & "!Macro1b" End With End With Set MyPopup = .Controls.Add(Type:=msoControlPopup) With MyPopup .Caption = "Popup 2" .BeginGroup = False Set MyButton = .Controls.Add(Type:=msoControlButton) With MyButton .Caption = "Button 2a" .Style = msoButtonCaption .BeginGroup = True .OnAction = ThisWorkbook.Name & "!Macro2a" End With Set MyButton = .Controls.Add(Type:=msoControlButton) With MyButton .Caption = "Button 2b" .Style = msoButtonCaption .BeginGroup = False .OnAction = ThisWorkbook.Name & "!Macro2b" End With End With .Width = 100 .Visible = True End With End Sub Sub Delete_Menu() On Error Resume Next CommandBars(MENU_NAME).Delete On Error GoTo 0 End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Mark Reynolds wrote: Hi All, I have a question for the Excel experts. How do you get custom commandbars to only open with a specific workbook, then close when the workbook is closed? I already use an auto_open macro for certain tasks. Would I insert commanbar = true statements into the macro? If I did this, how would I get the commandbar(s) to automatically close when I exit the workbook? Thanks for any help. Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel CommandBars | Excel Discussion (Misc queries) | |||
CommandBars("Insert").Enabled question | Excel Programming | |||
CommandBars | Excel Programming | |||
Built in Commandbars | Excel Programming | |||
Built In CommandBars | Excel Programming |