View Full Version : Toolbar macro won't run
jamieboss
08-21-2010, 06:56 PM
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,
'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
mikerickson
08-21-2010, 10:25 PM
If the command bar is to have the name ToolBari, it must be given that name when it is created.
Try this in ThisWorkbook
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
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()
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
jamieboss
08-22-2010, 09:51 AM
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
08-23-2010, 06:22 AM
Hey Jamie,
This is something I use on a daily basis:
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
As for the toolbar being run when you open the workbook and close the workbook, this is what I use:
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
jamieboss
08-23-2010, 07:09 AM
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
08-23-2010, 09:33 AM
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
08-23-2010, 09:44 AM
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
08-26-2010, 06:24 AM
"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
08-26-2010, 10:33 AM
Thanks guys. That does help my understanding of what's going on.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.