Tool Bar portability

  • Thread starter Thread starter Bill D.
  • Start date Start date
B

Bill D.

I am developing a workbook that will be given to a client to be placed
on their server and used by several individuals in the compnay. I want
to create a custom tool bar that will display when the workbook opens.
Will a custom tool bar created on my computer be part of the workbook or
does it remain only in my copy of Excel? If so, how can I attach the
custom tool bar to the workbook so when I transfer it to the company the
tool bar will be available to users? Will each user have to load the
custom tool bar into their own copy of Excel?

Bill

There are 10 types of people; those that understand binary and those
that don't.
 
Hi Bill,

You could either build the tool,bar when the workbook opens and destroy at
close, or attach it to the workbook. Do this by going to the View menu,
choose Tool Bars, then Customize. In that dialog, select the Toolbars tab
and click Attach. Select your command bar in the left side list box and
click Attach.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bill,

The best option is to create the commandbar on the fly, when the workbook is
opened, and delete the commandbar when the workbook is closed. Attaching a
commandbar to a workbook is a bad choice for a few reasons: the commandbar
gets permanently attached to the user's library file, and will open anytime
the user starts Excel, and then any click on the commandbutton will try to
open your file. To get around that, follow these instructions and example
code.

In the workbook's Thisworkbook object code module, place the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandbar
End Sub

Private Sub Workbook_Open()
CreateCommandbar
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("My Bar").Visible = True
Exit Sub
NotThere:
CreateCommandbar
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("My Bar").Visible = False
End Sub

In a regular code module, place the following:

Dim myBar As CommandBar
Dim myButton As CommandBarButton

Sub CreateCommandbar()

On Error Resume Next
DeleteCommandBar

Set myBar = Application.CommandBars.Add("My Bar")
With myBar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "SayHello"
End With
End With

End Sub

Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub

Sub SayHello()
MsgBox "Hello there"
End Sub

You can add as many buttons or other menu items as you like.

HTH,
Bernie
MS Excel MVP
 
There are probably numerous ways of doing this but they way I've done
this kind of thing in the past is to create a new tool bar in the
auto_open routine. That way every time anyone opens the spreadsheet it
will automatically creat a toolbar for the user to use.

for example:

On Error GoTo not_found
If (Application.CommandBars.Item("tase pplots").Visible) Then
GoTo check_buttons
not_found:
' no such toolbar exists so set one up
On Error GoTo the_end
Application.CommandBars.Add(Name:="tase pplots").Visible = True
Set menu_bar = CommandBars("tase pplots")
With menu_bar
Position = msoBarTop
End With
'
check_buttons:
' menu bar exist sso add buttons or whatever ...
 
Thanks all.

A lot of things to try here. I believe I will opt for creating and
destroying the toolbar each time the workbook opens.

Bill

There are 10 types of people; those that understand binary and those
that don't.
 
Back
Top