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 he

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

Have fun!

Doug

"Tom Joseph" wrote in message
...
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 InsightT 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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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