Question on custom commandbars

[Mark Reynolds]

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

[Peter Atherton]

Mark

You have to use VB. This open a dialog box and a ToolBar.
You will need a before_Close macro to hide them.

Sub auto_Open()
Application.CommandBars("3-D Settings").Visible = True
ActiveSheet.ShowDataForm
End Sub

Sub before_close()
Application.CommandBars("3-D Settings").Visible = False
End Sub

>.
>

[Rob van Gelder]

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 here:
http://www.vangelder.co.nz/excel/index.html

Rob

"Mark Reynolds" <mre...@hotmail.com> wrote in message
news:apk01096k9mn1j864...@4ax.com...

[Andy]

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/MenuActiveWbDemo.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" <mre...@hotmail.com> wrote in message
news:apk01096k9mn1j864...@4ax.com...

[Jon Peltier]

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/
_______