How to close a toolbar on exit of workbook - current code not work

[Tom Joseph]

Can someone please help me? The following code is not working. I would like
to close a toolbar on exit of a workbook.

Thanks.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteBar
End Sub

Sub DeleteBar()
Application.CommandBars("Dashboard Controls").Delete
End Sub

 

[Peter T]

Your code should work fine assuming a commandbar with that name exists.
Unless certain, normally best to use
On Error resume next
' delete code
On Error goto 0

Maybe you have the commandbar "attached" to a workbook which is causing
confusion.

Regards,
Peter T

 

[Tom Joseph]

Hi Peter,

Thanks for the note. Here is how the command bar is loaded. Is this
"attached" as you mention below? If this is not the proper approach, can you
please tell me how to resolve?

Thanks,

Tom


Sub Autpen()

Dim cb As CommandBar
Dim cbb As CommandBarButton

Set cb = Application.CommandBars.Add("Dashboard Controls", _
msoBarFloating, False, True)
cb.Enabled = True
cb.Visible = True

Set cbb = cb.Controls.Add(msoControlButton)
cbb.Style = msoButtonIconAndCaption
cbb.Caption = "Refresh Data"
cbb.FaceId = 159
cbb.OnAction = "InitializeDataInput2"

Set cbb = cb.Controls.Add(msoControlButton)
cbb.Style = msoButtonIconAndCaption
cbb.Caption = "Generate Reports"
cbb.FaceId = 433
cbb.OnAction = "ShowCommandPopupGenerateReports"

Set cbb = cb.Controls.Add(msoControlButton)
cbb.Style = msoButtonIconAndCaption
cbb.Caption = "Print Reports"
cbb.FaceId = 4
cbb.OnAction = "ShowCommandPopupPrintReports"

Set cbb = cb.Controls.Add(msoControlButton)
cbb.Style = msoButtonIconAndCaption
cbb.Caption = "eMail Reports"
cbb.FaceId = 258
cbb.OnAction = "CreateAndEmailReports"

End Sub

 

[Peter T]

At a glance your code looks fine, nothing there that "attaches" it to your
workbook. Though it's always worth "attempting" to delete the bar first,
under on error resume next, just in case it wasn't deleted last time.

A commandbar might have been attached previously, with the workbook active
look at
Customize toolbars, Attach...

or maybe
If Not ThisWorkbook.CommandBars Is Nothing Then
MsgBox ThisWorkbook.CommandBars(1).Name
End If

Regards,
Peter T

 

[Tom Joseph]

Thanks again. I will give it a try.

At a glance your code looks fine, nothing there that "attaches" it to your
workbook. Though it's always worth "attempting" to delete the bar first,
under on error resume next, just in case it wasn't deleted last time.

A commandbar might have been attached previously, with the workbook active
look at
Customize toolbars, Attach...

or maybe
If Not ThisWorkbook.CommandBars Is Nothing Then
MsgBox ThisWorkbook.CommandBars(1).Name
End If

Regards,
Peter T

 

[Doug Glancy]

Tom,

In addition to Peter's advice, I'd put the Make and Delete bar events in the
workbook's Activate and Deactivate events, for two reasons. The menu then
appears and reappears with the workbook, so if you leave the workbook open,
but switch to another workbook, the menu disappears. Also, it eliminates
the problem with BeforeClose, which is that if a user Cancels the close, the
workbook is still open, but the menu has already been deleted in the
BeforeClose event. So I'd do something like this:

Option Explicit

Private Sub Workbook_Activate()
Call MakeBar
End Sub

Private Sub Workbook_Deactivate()
Call DeleteBar
End Sub

Sub MakeBar()
Dim cb As CommandBar
Dim cbb As CommandBarButton

Call DeleteBar
Set cb = Application.CommandBars.Add(Name:="test", Position:=msoBarFloating,
temporary:=True)
With cb
..Visible = True
Set cbb = cb.Controls.Add(Type:=msoControlButton)
With cbb
.Caption = "testButton"
End With
End With
End Sub

Sub DeleteBar()
On Error Resume Next
Application.CommandBars("test").Delete
End Sub

hth,

Doug

 

[Tom Joseph]

Hi Doug,

I really appreciate the help. I think I have faithfully reproduced your
code, but it still is not working. THere is no toolbar appearing when I load
the workbook. It appears if I run Sub MakeBar. It does not close when I
close the workbook.

Do the private subs have to be in a specific module or class module to work?

Is there something else that might be happening? Here is my code:

Option Explicit

Private Sub Workbook_Activate()
Call MakeBar
End Sub

Private Sub Workbook_Deactivate()
Call DeleteBar
End Sub


Sub DeleteBar()
On Error Resume Next
Application.CommandBars("Dashboard Controls").Delete
End Sub


Sub MakeBar()

Dim cb As CommandBar
Dim cbb As CommandBarButton

Call DeleteBar
Set cb = Application.CommandBars.Add("Dashboard Controls", _
msoBarFloating, temporary:=True)
With cb
.Visible = True
Set cbb = cb.Controls.Add(Type:=msoControlButton)
With cbb
.Caption = "Refresh Data"
.Style = msoButtonIconAndCaption
.Caption = "Refresh Data"
.FaceId = 159
.Enabled = True
.Visible = True
.OnAction = "InitializeDataInput2"

End With
End With

End Sub

 

[Doug Glancy]

Tom,

I'm really sorry. I should have mentioned the Activate/Deactivate routines
have to be in the ThisWorkbook module. The Make and Delete can be there or
in a regular module.

Let me know if that gets it going.

Doug

 

[Tom Joseph]

Hi Doug.

It works perfectly! Thanks very much.

Could you help with one other syntax question? I have several other buttons
to add to this command bar, but I am unfamiliar with the convention you used
here. Could you tell me how to enable the button I have commented out?

Thanks.

Sub MakeBar()

Dim cb As CommandBar
Dim cbb As CommandBarButton

Call DeleteBar
Set cb = Application.CommandBars.Add("The Performance Insightâ„¢ Dashboard
Controls", _
msoBarFloating, temporary:=True)
With cb
.Visible = True
Set cbb = cb.Controls.Add(Type:=msoControlButton)
With cbb
.Caption = "Refresh Data"
.Style = msoButtonIconAndCaption
.Caption = "Refresh Data"
.FaceId = 159
.Enabled = True
.Visible = True
.OnAction = "InitializeDataInput2"


'Set cbb = cb.Controls.Add(msoControlButton)
'cbb.Style = msoButtonIconAndCaption
'cbb.Caption = "Generate Reports"
'cbb.FaceId = 433
'cbb.OnAction = "ShowCommandPopupGenerateReports"


End With
End With

End Sub

 

[Doug Glancy]

Tom,

The With/End With pair allows you to refer to multiple properties of the
control without specifying the control over and over. So here, we
repeatedly set the cbb variable to the most recently added button and then
use the With/End With to refer to 4 properties of that button. Here is how
the code should go:

Sub MakeBar()
Dim cb As CommandBar
Dim cbb As CommandBarButton

Call DeleteBar
Set cb = Application.CommandBars.Add(Name:="The Performance InsightT
Dashboard Controls", Position:=msoBarFloating, temporary:=True)
With cb
.Visible = True
Set cbb = .Controls.Add(Type:=msoControlButton)
With cbb
.Caption = "Refresh Data"
.Style = msoButtonIconAndCaption
.Caption = "Refresh Data"
.FaceId = 159
.OnAction = "InitializeDataInput2"
End With
Set cbb = .Controls.Add(Type:=msoControlButton)
With cbb
.Style = msoButtonIconAndCaption
.Caption = "Generate Reports"
.FaceId = 433
.OnAction = "ShowCommandPopupGenerateReports"
End With
End With

End Sub

My code last time was sloppy regarding the With's, but here I think it's
okay. So in the lines:
Set cbb = .Controls.Add(Type:=msoControlButton)
the ".Controls" is the same as "cb.Controls" but the "cb" doesn't need to be
specified because of the "With cb" above it.

In the lines:
With cbb
.Caption = "Refresh Data", etc,
".Caption" is the same as "cbb.Caption"

With/End With saves some resources, especially in some situations, becuase
you are only referring to the object once.

Looking ahead, you can see that using the same "cbb" variable multiple times
to refer to the most recently added control is like a loop. And a loop is
just what you want for more complex menus. What many people do for complex
menus is a table driven loop, like here:

http://spreadsheetpage.com/index.php/file/menu_maker/

Have fun!

Doug

 

[Tom Joseph]

Thanks, Doug. I appreciate all the help.

Best,

Tom