Toolbar macro won't run

[jamieboss]

Hi there,

I'm trying to write VBA code that creates a custom toolbar with buttons to run various macros. The aim is to have the toolbar added when the workbook opens and then deleted when it's closed. So far neither subroutines will run (the before close routine is listed here first). These two modules are in the 'this workbook' module. Can anyone tell me what I'm doing wrong?

Thanks,

[vba]
'Sub to delete the toolbar when the workbook is closed
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.CommandBars("ToolBari").Delete

End Sub

'And here is the macro to make the toolbar with one control button

Private Sub Workbook_Open()

Dim ToolBari As CommandBar
Dim CalcOn As CommandBarButton

Set ToolBari = Application.CommandBars.Add

With ToolBari

.Visible = True
.Position = msoBarFloating
.Protection = msoBarNoCustomize + msoBarNoChangeVisible
End With

'This is the line that won't run
Set CalcOn = CommandBars("ToolBari").Controls.Add _
(Type:=msoControlButton)

With CalcOn
.FaceId = 300
.OnAction = "TurnOnCalculate"
.Caption = "Turn Calc On"
.Enabled = True

End With

End Sub
[/vba]

[mikerickson]

If the command bar is to have the name ToolBari, it must be given that name when it is created.
Try this in ThisWorkbook
[VBA]Const barName As String = "ToolBari"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars(barName).Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim ToolBari As CommandBar
Dim CalcOn As CommandBarButton

With Application.CommandBars
On Error Resume Next
.Item(barName).Delete
On Error GoTo 0
Set ToolBari = .Add(Name:=barName, temporary:=True)
End With

With ToolBari
.Visible = True
.Position = msoBarFloating
' remove line: .Protection = msoBarNoCustomize + msoBarNoChangeVisible
End With

Set CalcOn = CommandBars(barName).Controls.Add(Type:=msoControlButton, temporary:=True)

With CalcOn
.FaceId = 300
.OnAction = "TurnOnCalculate"
.Caption = "Turn Calc On"
.Enabled = True
End With

ToolBari.Protection = msoBarNoCustomize + msoBarNoChangeVisible
End Sub[/VBA]

Once protection is applied, the control can't be changed, even by code. Protection should be added last.
The .Temporary argument of .Adding sounds like it would work here.

This is another version of the Open event()
[VBA]Const barName As String = "ToolBari"

Private Sub Workbook_Open()
With Application.CommandBars
Rem delete old command bar
On Error Resume Next
.Item(barName).Delete
On Error GoTo 0

With .Add(Name:=barName, temporary:=True)
.Visible = True
.Position = msoBarFloating

With .Controls.Add(Type:=msoControlButton, temporary:=True)
.FaceId = 300
.OnAction = "TurnOnCalculate"
.Caption = "Turn Calc On"
.Style = msoButtonIconAndCaption
.Enabled = True
End With

.Protection = msoBarNoCustomize + msoBarNoChangeVisible
End With
End With
End Sub[/VBA]

[jamieboss]

Mike,

Many thanks! I tried the first routine you suggested and it works great by adding the word "application" to this line:

Set CalcOn = CommandBars(barName).Controls.Add(Type.....

becomes

Set CalcOn = Application.CommandBars(barName).Controls.Add(Type

You explain that "If the command bar is to have the name
ToolBari, it must be given that name when it is created."
In my original code, I thought the following line did that:

Set ToolBari = Application.CommandBars.Add

or this line in your code:
Set ToolBari = .Add(Name:=barName, temporary:=True)

Do these two lines not assign the name to the toolbar?

I thought this next line was optional:

Const barName As String = "ToolBari"

Is this line performing the function of giving the toolbar
a name (the step my original code missed)?

Sorry to be asking elementary questions however I suspect this
step is key to my understanding VBA better.

Thanks again so much,

Jamie

[lynnnow]

Hey Jamie,

This is something I use on a daily basis:

[VBA]
Sub ToolbarLincoln()
Dim popUpBar As CommandBar
Dim newButton As CommandBarControl

On Error Resume Next
CommandBars("Lincoln").Delete 'Change this name in the quotes
On Error GoTo 0

Set popUpBar = CommandBars.Add(Name:="Lincoln", Position:=msoBarTop, Temporary:=True) ' change here too.

Set newButton = popUpBar.Controls.Add(Type:=msoControlButton, ID:=1)
With newButton
.FaceId = 352
.Enabled = True
.Caption = "Make this the caption"
.OnAction = "Macro to be run"
.Style = msoButtonCaption
.TooltipText = "This is optional"
End With

popUpBar.Visible = True

End Sub
[/VBA]

As for the toolbar being run when you open the workbook and close the workbook, this is what I use:

[VBA]
Private Sub Workbook_Activate()
Call ToolbarLincoln
End Sub

Private Sub Workbook_Deactivate()
'call routine that removes menu for this workbook
For Each Bar In Application.CommandBars
If Not Bar.BuiltIn And Bar.Visible Then Bar.Delete
Next
End Sub
[/VBA]

[jamieboss]

Thanks Lynn, I found that very helpful since that code is very easy to follow. I now see that I need to use the 'name' property to assign a name.

We use an excel application that I developed as the basis of our small business. Compared to working in the corporate world it can be tricky since there's noone above me to ask questions when I get stuck. I really appreciate the help since books can't teach you everything.

Thanks again.

[jamieboss]

So after running a few experiments I guess I'm still not absolutely clear on the functioning of the 'name' property of a toolbar object. Lynn's code, uses the line:

Set popUpBar = CommandBars.Add(Name:="Lincoln",...

Later in the routine, the command bar is referred to as "popUpBar".
I thought that was the name of the toolbar assigned by the Set
statement? I was not aware it was necessary to use "Name" property of
the object. I do see however that this allows you to refer to the
object by name ("Lincoln") when we delete the toolbar. Why can't
I leave out the name property, then just delete the toolbar by
using a statement like:

Application.popUpBar.delete


Many thanks in advance for any help provided.

[lynnnow]

Jamie,

The name of the toolbar is "Lincoln" and since it is an object it has to be set to be initialized. Then later on reference is made to the object, not the name of the object.

Object = popupbar
Name of object = Lincoln
Controls to be added to popupbar (object) not "Lincoln" (name of the object)

You can directly delete that specific toolbar in that manner (if it were run in the same module), however, when the macro to create the toolbar was run, the popupbar object was set. When the delete macro is run, the object is not set again, instead just uses the .BuiltIn property to check if the popupbar is a builtin one or a user defined one and deletes the user-defined popupbar, leaving the builtin menu bar/popupbars intact.

HTH

[mikerickson]

"Lincoln" is the name of the object.
popupbar is the name of a variable in a VB routine.

Once the workbook has been closed and re-opened, the association between a control and its name ("Lincoln") remains. The assosication between a control and a variable representing that control lasts only as long as the variable does. (Or until the variable is assigned a different value.)

[jamieboss]

Thanks guys. That does help my understanding of what's going on.